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:
tinyint 255,
smallint  32767,
int 2147483647
bigint 9223372036854775807

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