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.
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)
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.