Tip: Make your Identity Keys go Further

  • Article

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

Comments

  • Anonymous
    April 06, 2009
    PingBack from http://www.anith.com/?p=26705

  • Anonymous
    April 06, 2009
    The only thing to look out for when doing this is that there are tools and frameworks out there that use the negative values to represent rows that haven't yet been committed to the database. For example if you create an identity key in a typed DataSet in Visual Studio, the increment automatically defaults to -1.  This is really helpful with debugging persistence issue as you can just look at the values and immediately know whether they've been saved or not just by checking if they're negative or postive.

  • Anonymous
    April 06, 2009
    I had never consider this until reading your post but I bet you could also do BIT IDENTITY(1,1) to limit the table to a single row.  Someone could use IDENTITY INSERT ON to create a second row but they'd have to really want to do it.  You might even be able to use BIT IDENTITY(0,2).  I would assume that would set the first record to 0 but the second couldn't be inserted because it would be too big.  I might have to play with that a bit to see how it works.  I suppose you could limit the number of rows more effectively using a trigger. This is not to say I think creating tables with a single row is a good idea.  It may indicate that you need to redesign something in your database.  I know single row tables are often used for settings and/or user options.  

  • Anonymous
    April 06, 2009
    heh :) this is a groovy tip :) My first thoughts are:

  1. Will i ever hit int.Max?
  2. If so, can i change it from int -> long?
  3. With db maintenance, is it more 'annoying' to type some sql queries with these big numbers AND having to remember to put the 'negative' symbol at the front? eg. select blah from blah where id = <big neg number> That said, i too never thought about an identity being a neg number! great tip! Hope we get more nugget tips like this, regularly :)