Revisando nuestras columnas IDENTITY

Es importante revisar en que valor se encuentran nuestras columnas identity para evitar un error en nuestras aplicaciones en horarios de producción.

El error que podría presentarse es el siguiente:

Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.

Los tipos de datos que usamos en nuestras columnas identity pueden ser las siguientes y el máximo valor que pueden soportar se muestra en la siguiente tabla:

El siguiente script nos puede ayudar a saber en que porcentaje de utilización se encuentran nuestras columnas identity, así como el tipo de datos que utiliza.

SQL Server 2005

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

SQL Server 2000. Con tablas del sistema

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

SQL Server 2000. Con vistass INFORMATION_SCHEMA

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