In SQL Server, you can use the T-SQL IDENT_INCR() function to return the increment value of an identity column. This is the increment value specified when creating the identity column.

Syntax

The syntax goes like this:

IDENT_INCR ( 'table_or_view' )

The table_or_view argument is an expression that specifies the table or view to check for a valid identity increment value.

Example 1 – Basic Usage

Here’s a basic code example.

SELECT IDENT_INCR('Pets') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

In this case, the identity increment value is 1.

Here’s another query that checks more tables.

SELECT 
  IDENT_INCR('Pets') AS Pets,
  IDENT_INCR('BestFriends') AS BestFriends,
  IDENT_INCR('Cities') AS Cities;

Result:

+--------+---------------+----------+
| Pets   | BestFriends   | Cities   |
|--------+---------------+----------|
| 1      | 10            | 10       |
+--------+---------------+----------+

Example 2 – Including the Schema

You can also include the schema in the argument.

SELECT IDENT_INCR('dbo.Pets') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 3 – Check All Tables in the Database

Here’s an example of checking all tables in the database for their identity seed value.

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME,   
  IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR  
FROM INFORMATION_SCHEMA.TABLES  
WHERE IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;

Result:

+----------------+----------------+--------------+
| TABLE_SCHEMA   | TABLE_NAME     | IDENT_INCR   |
|----------------+----------------+--------------|
| dbo            | ConstraintTest | 1            |
| dbo            | Event          | 1            |
| dbo            | Scoreboard     | 1            |
| dbo            | Pets           | 1            |
| dbo            | BestFriends    | 10           |
| dbo            | Cities         | 10           |
| dbo            | Colors         | 1            |
+----------------+----------------+--------------+

About The Return Value

The return value for IDENT_INCR() is numeric(@@MAXPRECISION,0)). This means that it’s a numeric data type with a fixed precision and scale.

The precision depends on the server. The @@MAXPRECISION argument returns the level used by decimal and numeric data types as currently set in the server. The precision specifies the maximum total number of decimal digits (it includes those on the left and the right sides of the decimal point).

The second argument specifies a scale of 0, which means there are no decimal digits to the right of the decimal place.

You can run the following query to find the precision level currently set on your server:

SELECT @@MAX_PRECISION AS [Max Precision];

Here’s the result on my system:

+-----------------+
| Max Precision   |
|-----------------|
| 38              |
+-----------------+

Creating an Identity Column

You can create an identity column by using the IDENTITY() property in your CREATE TABLE or ALTER TABLE statement.

Another way to create an identity column is the IDENTITY() function. This enables you to create an identity column when using a SELECT INTO statement to transfer data from one source to another.