The IDENTITY columns, or 'auto number' columns as some people call them, are auto incrementing columns provided by SQL Server. There can only be one IDENTITY column per table. You just have to provide a base value, and an increment value, and SQL Server will take care of incrementing this column automatically. Some people like these, and some don't, but the truth is, IDENTITY columns are gaining popularity, and many production systems, including critical ones are using IDENTITY columns these days. So, it is important to keep an eye on these columns, to make sure they are not reaching the limit of their base data type. For example, if you created an IDENTITY column of smallint datatype, its values can go upto 32767. If you try to insert anymore rows, you will get the following error:
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.
If this table happens to be a part of a critical production system, then you are in trouble. You will have to do something about it to resolve it. If the data from this table can be deleted, then delete the data using TRUNCATE TABLE command. TRUNCATE TABLE resets the IDENTITY column to its base value. The DELETE command doesn't do this. But then, if this table is referenced by a foreign key, then TRUNCATE TABLE is not allowed on this table. Your other option is to run DBCC CHECKIDENT on your table with RESEED option.
An IDENTITY column of tinyint datatype can go upto 255, smallint can go upto 32767, int can go upto 2147483647 and bigint can go upto 9223372036854775807.
You can proactively monitor these IDENTITY columns, to avoid getting into such problems. If you can see in advance, that an IDENTITY column is reaching its limit, then you could do something about it, before it reaches the limit. The other day, one of my friends was trying to automate a process that checks all the IDENTITY columns in a database and reports on how far away those columns are from the limit. He was using a cursor to go through all the tables in the database, and running a "SELECT MAX(IdentityCol) FROM TableName" on all the tables that have an IDENTITY column. It would take ages to run on a database with many big tables. It can be simplified into one simple query using IDENT_CURRENT function. That's what I did, and thought it will be useful for other DBAs as well. So, here I am writing about it.
There are three different versions of this procedure. First one is for SQL Server 2005. The second and third versions work in SQL Server 2000. You will have to create this procedure in the database of your interest. And run it as shown below:
This procedure below, displays information about all IDENTITY columns in the database, and shows you the percentage of IDENTITY values already used. If you are seeing any IDENTITY columns that have used up 80% or more values, then you need to start thinking about it. You could customise this procedure to automatically email you or log an error if there are any IDENTITY columns that are nearing the limit. You could also schedule this procedure as an SQL Agent job, so that it checks these columns regularly. Any new IDENTITY columns added to the database will automatically get picked up by this query.
A quick note about 64 bit SQL Server. Even though SQL Server 64 bit editions can access a lot more memory inherently, than the 32 bit systems could - the IDENTITY columns are still limited to the limits imposed by the base datatypes. I'm writing this because, someone recently asked me if int data type can store a higher number in 64 bit server, compared to a 32 bit server.
You could read the original article from the following link: http://vyaskn.tripod.com/sql_server_check_identity_columns.htm