Every IDENTITY column has two basic attributes: SEED and INCREMENT. The SEED attribute stores the ROOT or Starting value. INCREMENT specifies the value to be added to the current IDENTITY value to generate the next value. The default value of both these attributes is 1. So unless you specify a different value, an IDENTITY column starts with 1 and increments by 1.

How to find the SEED value of an IDENTITY column?

You can use the system function IDENT_SEED() to retrieve the SEED value of an IDENTITY column. for example:

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

SELECT IDENT_SEED('Employees') AS IdentitySeed
/*
IdentitySeed
---------------------------------------
1
*/

How to find the INCREMENT value of an IDENTITY column?

Just like IDENT_SEED(), there is another function that returns the INCREMENT value of an IDENTITY column: IDENT_INCR(). The following example demonstrates that.

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

SELECT IDENT_INCR('Employees') AS IdentityIncrement
/*
IdentityIncrement
---------------------------------------
1
*/

How to find the current IDENTITY value of a table?

The system function IDENT_CURRENT() returns the last generated IDENTITY value of a table. If the table is empty (no records generated yet or truncated), this function will return the IDENTITY SEED. Let us look at an example:

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 1

INSERT INTO Employees(Name) SELECT 'Jacob'
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 1

INSERT INTO Employees(Name) SELECT 'Steve'
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 2

SQL Server 2005 Books Online documentation incorrectly states that the function returns NULL if called for a table that is empty (no records inserted yet or the table is truncated). The above example shows that in such case, SQL Server returns the IDENTITY SEED value, instead of NULL value. This documentation error is corrected in SQL Server 2008 Books Online Documentation.

Retrieve the SEED, INCREMENT and LAST IDENTITY value of all tables in a database

Let us try to write a query that returns the SEED, INCREMENT and LAST IDENTITY value of all tables in the AdventureWorks database. Here is the query:

WITH cte AS (
    SELECT
        s.name + '.' + t.name AS TableName,
        c.name AS ColumnName
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON c.object_id = t.object_id
        AND c.is_identity = 1
)
SELECT 
    TableName,
    ColumnName,
    IDENT_SEED(TableName) AS Seed,
    IDENT_INCR(TableName) AS Increment,
    IDENT_CURRENT(TableName) AS LastIdentity
FROM cte
/*
TableName                      ColumnName                Seed        Increment   LastIdentity
------------------------------ ------------------------- ----------- ----------- ------------
Person.Address                 AddressID                 1           1           32521
Production.ProductReview       ProductReviewID           1           1           4
Production.TransactionHistory  TransactionID             100000      1           213442
Person.AddressType             AddressTypeID             1           1           6
Production.ProductSubcategory  ProductSubcategoryID      1           1           37
...
...
*/

Question to Readers: Can you think of a scenario where you need to know the SEED, INCREMENT or CURRENT IDENTITY value of a table?