Identity columns are a commonly used feature in SQL Server that provide an automatic incrementing value for a column in a table. They are often used as primary keys or unique identifiers for records in a table.

In a previous blog post, we discussed a query that can be used to find the seed values, increment values, and current identity column value of a table. This query has been widely popular among SQL Server users.

Recently, SQL expert Mark Hickin made an interesting modification to the query. He added the ability to display the upper limits of the data type used in the query. This enhancement provides even more valuable information about the identity values.

However, the original query had a limitation – it only worked when the increment value and seed value were both set to 1. If you had a different value for either of these settings, you would need to modify the script.

Fortunately, SQL Server expert Harsh has provided an amazing script that addresses this limitation. The modified query takes into account the custom increment and seed values and provides accurate results.

Here is an example of the modified query:

SELECT Seed, Increment, CurrentIdentity, TABLE_NAME, DataType, MaxPosValue, 
       FLOOR((MaxPosValue - CurrentIdentity) / Increment) AS Remaining,
       100 - 100 * ((CurrentIdentity - Seed) / Increment + 1) / 
       FLOOR((MaxPosValue - Seed) / Increment + 1) AS PercentUnAllocated
FROM (
    SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed,
           IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment,
           IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity,
           TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME,
           UPPER(c.DATA_TYPE) AS DataType,
           FLOOR(t.MaxPosValue / IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME)) * 
           IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS MaxPosValue
    FROM INFORMATION_SCHEMA.COLUMNS AS c
    INNER JOIN (
        SELECT name AS Data_Type, POWER(CAST(2 AS VARCHAR), (max_length * 8) - 1) AS MaxPosValue
        FROM sys.types
        WHERE name LIKE '%Int'
    ) t ON c.DATA_TYPE = t.Data_Type
    WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
) T1
ORDER BY PercentUnAllocated ASC

This query provides detailed information about the identity column, including the seed value, increment value, current identity value, table name, data type, maximum possible value, remaining values, and the percentage of unallocated values.

By using this query, you can easily analyze and monitor the identity column values in your SQL Server database. It can be particularly useful when you have custom increment and seed values set for your identity columns.

Thank you, Harsh, for sharing this valuable script with the SQL Server community. It is a great addition to our toolkit for managing identity columns.