IDENTITY a fraud?

Published 12 May 09 06:58 PM | MartinBell

One of the tips Tony gave during his talk at last weeks Manchester User Group was not to start your identity values at the default (zero) but start them at the lowest possible value for the datatype you are using. This is a great tip as it doubles the range of possible values and it happened to coincide with some thoughts I had for a connect suggestion.

Reaching the maximum is not necessarily the end of the world, if you are using a smaller datatype it is possible to change the datatype of the column to be one that is larger e.g. change an int to a bigint. But is not always that straight forward as you may have to drop primary keys, indexes and foreign keys and change the data type of any referencing columns and also change any variables or temporary tables in stored procedures or triggers etc. etc…

Another alternative is to set the identity seed back to the minimum possible value and start to use the values you missed. This raises the question… “Why didn’t we do that in the first place?” Of course when you hit zero you will start to have problems again, but it will give you time to think and plan.

Although bigint can hold 2^64 values which according to Brian Moran in his SQL Server Magazine article will take 58,494,241 years to fill at 10,000 inserts per second, starting at zero will halve that time, but you’re not going to be around to see it run out… if all goes to plan!! (I have seen some post-migration issues because seed values have been incorrectly set, so be warned!!)

If you have an integer identity value there are 2^32 possible values, so at the rate in Brian’s example it will only take approximately 0.0136 years to fill which is less than 4.98 days even if you start at the lowest possible value.

By now, you use identity values in your database it may have dawned on you that perhaps you should be checking the values that are in your identity columns and may be planning for them being full? I would hazard a guess that very few DBAs actually actively monitor these values.

Although DBCC CHECKIDENT can give you the current maximum value and the max possible value for an identity column, and IDENTITY_INCR will give you the current increment (which may not be 1!).  I don’t know of any system function that will tell you how many rows you can insert before getting errors, so I wrote a little script that would give me this information.

Finally here is a link to the connect item I raised vote for it if you think it is a good idea!

Filed under:


# Dew Drop - May 13, 2009 | Alvin Ashcraft's Morning Dew said on May 13, 2009 02:27 PM:

Pingback from  Dew Drop - May 13, 2009 | Alvin Ashcraft's Morning Dew

This Blog

SQL Blogs