Quick T-SQL Tip #2: Identity Columns Seed Value

This post really deals with high volume tables with an identity column, but the idea is something that everyone should at least be familiar with.

Quite often I see table creation scripts that start like this:

Create Table ATable
(
ID int not null identity(1,1)
...

And while that is perfectly legal and will work with no issues, if this table is going to be a high volume table, you should consider using the entire Int, not just half of one.

What??

The Int data type spans -2,147,483,648 thru 2,147,483,647.  If your seed is 1, you lose 2,147,483,649 values that could be used.

I have seen production situations where they have run out of identity values because the seed was 1.
So, next time you create an Identity, ask yourself: "Two billion, or four billion?"

and then 

Create Table ATable
(
ID int not null identity(-2147483648,1)
...

peace