Keep a check on your IDENTITY columns in SQL Server
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:
EXEC dbo.CheckIdentities
GO
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.
Here's a screen shot of output from the AdventureWorks sample database in SQL Server 2005.
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:
EXEC dbo.CheckIdentities
GO
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.
Here's a screen shot of output from the AdventureWorks sample database in SQL Server 2005.
/* The SQL Server 2005 version of the stored procedure. It uses new catalog views */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.system_type_id
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue,
CASE c.system_type_id
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC
END
If you try to create the above stored procedure in SQL Server 2000, you will get the following error:
Server: Msg 195, Level 15, State 10, Procedure a, Line 4
'SCHEMA_NAME' is not a recognized function name.
So, here are some SQL Server 2000 compatible versions.
/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(USER_NAME(t.uid))+ '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.xtype
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) AS CurrentIdentityValue,
CASE c.xtype
WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM syscolumns AS c
INNER JOIN
sysobjects AS t
ON t.id = c.id
WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1
AND OBJECTPROPERTY(t.id, 'isTable') = 1
ORDER BY PercentageUsed DESC
END
/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS TableName,
c.COLUMN_NAME AS ColumnName,
c.DATA_TYPE AS 'DataType',
IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
CASE c.DATA_TYPE
WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 2147483647
WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 32767
WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 255
END AS 'PercentageUsed'
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN
INFORMATION_SCHEMA.TABLES AS t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1
AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint')
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY PercentageUsed DESC
END
