Member-only story

How much is left until the Identity Column reaches its Max Value?

HKN MZ
3 min readApr 30, 2024

--

This code provides insight into the current values of identity columns in the database and calculates the percentage of their usage relative to their maximum possible value.

select
object_schema_name (object_id) SchemaName,
object_name (ic.object_id) as TableName,
ic .name as ColumnName,
convert (bigint, isnull(ic.last_value, 0)) as LastValue,
case when t.name = 'tinyint' then 255
when t.name = 'smallint' then 32767
when t.name = 'int' then 2147483647
when t.name = 'bigint' then 9223372036854775807
end as MaxValue,
ceiling(100.0*(convert(bigint, isnull(ic.last_value, 0))/
( case when t.name = 'tinyint' then 255
when t.name = 'smallint' then 32767
when t.name = 'int' then 2147483647
when t.name = 'bigint' then 9223372036854775807
end))) as [%Percent]
from sys.identity_columns ic
inner join sys.types as t
on ic.system_type_id = t.system_type_id
order by [%Percent] desc

The SELECT statement retrieves the following information:

  • object_schema_name(object_id) AS SchemaName: Retrieves the schema name of the table containing the identity column.
  • object_name(ic.object_id) AS TableName: Retrieves the name of the table containing the identity column.
  • ic.name AS ColumnName: Retrieves the name of the identity column.
  • CONVERT(BIGINT, ISNULL(ic.last_value, 0)) AS LastValue: Retrieves the last generated identity value for the column.

--

--

HKN MZ
HKN MZ

Written by HKN MZ

I am writing about Sql Server, Elasticsearch and Python. İ am an Database Administrator on SQL Server and Elasticsearch more than 5+ years.

No responses yet