Assign Unique ID within groups of records - Start with 0 or 1

This is probably a subjective question, but I would like to know your opinion. It's somehow related to this question, but I would like to now if you would start off in databases with 0 or 1. Auto-incremental ids in sqlserver start off from 1, so my subjective opinion wouuld be to start off with 1. On the ot开发者_StackOverflowher hand, in most programming languages, indexes start off with 0.

Personally, I would start any ids in the database at 1, not 0. I would then treat 0 as an invalid value for the identity.

The reason for this is that when you deal with objects from the database in code, it is common to have the identity value stored in an integer type field. In some programming languages (I'm speaking with C# and Java experience), the default value for uninitialised integer fields is 0, meaning that if the identity field was not set in code, you would get the same value as that of a row in the database with an identity of 0. This leads to ambiguity and possibly hard to track down bugs.

We reserve the index 0 for the non-applicable item and then let the autoincrement proceed, in this way we can always join the table wihout left join or null values. For example in a Brands table we could have:

• 0: no brand/no car
• 1: Ford
• 2: Toyota
• and so on....

and in the Inventory facts table we can have unbranded items and still have a perfect join with the Brands table.

Thomas

From my perspective indexes in code just differ from indexes used in Databases, so why would you wanna compare those two anyway. Just start with whatever you like yourself. It's not like you will get any problems using both ways anyway.

Surrogate primary key shouldn't rely on any business processes/logic in application. In this case any valid PK value and sequence is good.

started from -1, only odds, only ended with 7, etc etc.

this doesn't matter, just because it's artificial key

As the number should be opaque to any uses of it, it shouldn't matter at all what you pick. You're unlikely to want to index all the entries by ID, you'd use a query to loop through them all.