Identity Columns- How close to the edge are you?

Published 09 May 11 09:27 PM | MartinCroft

Day 9. How close is your data to the edge! Integer identity columns that increment by 1, have been known to run out of numbers. I have seen it occur on a few occasions on highly transactional systems. However there is really no excuse for getting caught out. The folwoing code easily shows you were your identity table columns are.

 

--2005
SELECT SCHEMA_NAME(schema_id) AS [Schema],name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY [Schema], [Table];
GO
--2000
SELECT Name from sysobjects 
WHERE OBJECTPROPERTY(id,'TableHasIdentity') = 1

 

The code below will show all your tables that have an identity seed on them and how full they are percentage wise. The trick is to catch them before they hit 100% and bring down the database as it can't insert any more rows!.

There are several options to fix, change the data to bigint, reseed the values ( if you don’t keep all the data such as archiving and you don’t need a unique ID) to a lower value,  or even start on a negative identity number to double the capacity!, but somehow that seems wrong. If it had been planned correctly it would already be the right data type!. Hey these thing happen when you inherit systems

Exact number data types that use integer data.

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

 SQL 2000 code 
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
 
 SQL  2005/2008 
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',
    CAST(CAST(GETDATE()as VARCHAR(12))as datetime) as ReportTime
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

Comments

No Comments