Returns the increment value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_INCR may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.
A. Returning the increment value for a specified table
The following example returns the increment value for the Person.Address table in the AdventureWorks2012 database.
USE AdventureWorks2012; 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 AdventureWorks2012 database that include an identity column with an increment value.
USE AdventureWorks2012; 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