Tip: Make your Identity Keys go Further

It is extremely common to use computer generated Primary Keys in a table. eg:

 OrderID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL

Most people set the seed to 1. I don’t know why. Perhaps because it is the default value, maybe they display their keys to end-users, possibly they’ve never really thought about it.

But be aware that a seed = 1 halves the range of possible values.

Recommendation

The Seed can be a negative value, so set it to the most negative value you can. eg:  If you define your table with “OrderID SMALLINT IDENTITY( -32,768,1) then you have doubled the possible number of rows the table can contain before needing to increase the width of the key to an INT.

Consider using OrderID INT IDENTITY( -2,147,483,648,1) or BIGINT IDENTITY( -9,223,372,036,854,775,808,1) or even TINYINT IDENTITY(0,1)

Enjoy,

   Dave

As always please post comments let me know if this is useful to you.

Thought for the day: (best read with a Forest Gump accent)

I have two twin sons. As the first one came out, he looked like a Pete, so I called him Pete. When the second one came out he looked exactly the same as his brother. So I called him re-Pete.

 

Technorati Tags: TSQL,SQL Server,Database Design