Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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