Fresh-from-the-printer : Sushi, Gaming, and Sql Server..:

Sushi, Gaming, and Sql Server..

Tuesday rocked! Full day of development, night full of Sushi Japan and gaming. Working all day on an asp.net application with utterly no success could only be topped off with excessive eating and gaming. After work, I headed out with the wife for some food and drinks. Those of you who know me know I do not eat sushi, but I love the chicken and steak combo. After the meal and some much needed drinks, it was off to the local game store to pick up Mercenaries, a Lucas Arts (Xbox) game that was released yesterday. While I was there, I figured I would also jump into the 20th century an purchase a couple of Wavebirds for the GameCube (another one of the best ideas I have ever had). Anyway, long story short, I spent the rest of the night enjoying the new Lord of the Rings: Battle for Middle Earth PC Game, Metriod Echoes on the Gamecube, and Mercenaries. What a brilliant Tuesday night. It was reminiscent of the days of lore when I was single and full of dead time. I will most likely have to stay up later than normal tonight and make up for my fun, but what the hell right?

Anyway, now for the technical stuff. I have an interesting question which I am sure will spur some sort of debate, but here goes.. Guids or Identity fields in SQL Server? What's your poison? Personally I am a fan of Guids when needed, which is logically most of the time. Unfortunately we are all bound to get into positions where we are debating the solution with ex-access people who are less concerned with the correct way and the more comfortable way. Anyway, I wanted to give some of the developers out there some ammunition, so here goes:

Joe's top 5 reasons that Guids are better identifiers than integers:

1. You will never run out of Guids, Identity has a limit of (2,147,483,647) and if you run out the query crashes

2. Replication is much harder to perform using Identity fields than Guids. When using a guid, you know there will not be any servers with the same value.'

3. There is a problem on tables with identities if the db server crashes. If it crashes while writing inserts, when the server is restarted, SQL Server will reset the next identity to a very large number/ For instance, if the current Identity is 50k when the server crashes, it will go up to 100,000,000. This is most likely a design feature, as the rollback does not know how many records are about to be written, so it bumps up the next identity to something it cannot hit. Since it does not reset the next Identity back down, a few of server crashes can easily put you over the 2 billion number pretty quick.

4. Developers can create Guids on the fly allowing them to save a server trip to the database after an insert.

5. Guids are far more secure and allow important id's to be use din a more general sense due to the lack of protection needed.

Having said that, the only downside to Guids is that they are a bit slower than and int (usually in the milo or pico seconds), but then again so is everything else. Having the guarantee and security of Guids far outweigh the minuscule hit in performance. Identifier is merely an adaptation of Access's "auto number" feature. It's really outdated and not preferred any more. Additionally, after 100,000 rows. guids actually perform better than numeric types on modern hardware due to the indexing optimizations (cascading of the Guid blocks) that MS has implemented.

What is your implementation of choice? Anyone out there know I am an idiot and want to tell me why. Let me know!