IDENT_INCR (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
SQL database in Microsoft Fabric

Returns the increment value specified when creating a table or view's identity column.

👁 Image
Transact-SQL syntax conventions

Syntax

IDENT_INCR ( 'table_or_view' ) 

Arguments

' table_or_view '
Is an expression specifying the table or view to check for a valid identity increment value. table_or_view can be a character string constant enclosed in quotation marks. It can also be a variable, a function, or a column name. table_or_view is char, nchar, varchar, or nvarchar.

Return Types

numeric(@@MAXPRECISION,0))

Exceptions

Returns NULL on error or if a caller doesn't have object view permission.

In SQL Server, a user can only view the metadata of securables they own or have permissions for. Without user object permission, a metadata-emitting, built-in function, such as IDENT_INCR, may return NULL. For more information, see Metadata Visibility Configuration.

Examples

A. Returning the increment value for a specified table

The following example returns the increment value for the Person.Address table in the AdventureWorks2025 database.

USE AdventureWorks2022; 
GO 
SELECT IDENT_INCR('Person.Address') AS Identity_Increment; 
GO 

B. Returning the increment value from multiple tables

The following example returns the tables in the AdventureWorks2025 database that includes an identity column with an increment value.

USE AdventureWorks2022; 
GO 
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; 

Here is a partial result set.

TABLE_SCHEMA TABLE_NAME IDENT_INCR 
------------ ------------------------ ---------- 
Person Address 1 
Production ProductReview 1 
Production TransactionHistory 1 
Person AddressType 1 
Production ProductSubcategory 1 
Person vAdditionalContactInfo 1 
dbo AWBuildVersion 1 
Production BillOfMaterials 1

See Also

Expressions (Transact-SQL)
System Functions (Transact-SQL)
IDENT_CURRENT (Transact-SQL)
IDENT_SEED (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
sys.identity_columns (Transact-SQL)