Livin' the life...

While building a database lately, I was called to make the decision on a primary key field.. now the way I usually do database implementation is to use an Identity and let the auto-increment take care of it. This time though while playing around a bit before heading to that solution I came across the GUID field type (a type that is also used for primary key or.. unique key requirements). To give you a little background on it though, I've included the snippet below..

"An alternative to using an auto incrementing value is to use the NewGuid method of a Guid object to generate a GUID, or globally unique identifier... The NewGuid method generates a 16-byte binary value that is created using an algorithm that provides a high probability that no value will be duplicated. In a SQL Server database, a GUID is stored in a uniqueidentifier column..." (MSDN)

Now, let's look at this excerpt properly:

1. "generates a 16-byte binary value" .. this usually is in the format of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Yea.. pretty long eh? That will mean some overhead will be involved in the lookups. Not exactly a feature you would like in searching, sorting, joining, etc. Also.. remember, computers work better with numbers, this GUID seems just like a long string.

2. "an algorithm that provides a high probability that no value will be duplicated" .. a.. high probability.. well, that's good .. but with Identity (auto-increment), there will be no duplications period.

Those are what I consider the top two reasons to probably stick with an Identity ( i.e. autoincrement value).

One of my key sources for insight into this topic was here. Even though there's no Author name (hence some might say it is not that 'credible'), at least he/she put links for where they sourced their information from.