Disclaimer: Personally, I prefer to use uniqueidentifier as my primary key data type. It cuts down on improper joins (similar looking values aren’t in every table, ie 1, 2, 3) and is infinitely portable among environments. I think the extra space and overhead is worth it.
We have some reference tables in our database at work. This itself isn’t abnormal, everyone has these. What made these tables special was that the IDENTITY Primary Key for the table started at 0. At first I thought that this was awesome. I know it is a debate for another day, but I love that my arrays, counters, etc start at 0 in C#. I know it may not be necessary anymore or whatever, but it comforts me, okay? 🙂
Things soon changed for the worse when I realized that 0 being a significant data value was a problem. “Why?”, you may ask. Well, when you just generically “new up” an int, what is the default value? That’s right, 0. So, if someone doesn’t set a property when trying to identify some reference value, in a 1-based table (or a GUID one) you will get an error because that isn’t a valid value. However, in a 0-based table, that “forgetfulness” will match right up with a value and go in no questions asked. You can’t validate it, because 0 is perfectly fine to have.
Lesson Learned: Zeroes are fine for arrays and loops, but keep them out of reference tables!
