ident-incr-transact-sql.md

title	IDENT_INCR (Transact-SQL)
description	IDENT_INCR (Transact-SQL)
author	VanMSFT
ms.author	vanto
ms.date	03/14/2017
ms.service	sql
ms.subservice	t-sql
ms.topic	reference
ms.custom	ignite-2025
f1_keywords	IDENT_INCR IDENT_INCR_TSQL
helpviewer_keywords	incremental values [SQL Server] IDENT_INCR function identity columns [SQL Server], IDENT_INCR function
dev_langs	TSQL

IDENT_INCR (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDEssNoVersion], 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 [!INCLUDEssSampleDBnormal] 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 [!INCLUDEssSampleDBnormal] 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)