Wednesday, March 21, 2007

Using the IDENTITY type

I read a great article on key field for SQL Server, primary a comparison between Identity and GUID.

While working with SQL Server, I always include an identity field in each table.
This is not necessarily used as an index, but to ensure that even if I mistakenly enter bad data into the table, each row will still be unique:
Suppose you don't have identity field, and you replicate a row's data. Now try deleting only one of the duplicates - you can't, since the server has no way of telling them apart, meaning you'll need to delete all of them (and probably re-enter the data). The identity field prevents that problem.

In addition, I have always been fond of using surrogate fields as my key over natural keys, since as stated in the article, even a supposedly unique natural key may be a duplicate, and that's without counting simple typing errors.

I agree with the writer GUID is not a good choice since it's not easy to work with.
However, I may use another surrogate key, which I generate myself, in addition to the identity column.

No comments: