Skip to content

ident-incr-transact-sql.md

Latest commit

 

History

History
107 lines (79 loc) · 4.35 KB

ident-incr-transact-sql.md

File metadata and controls

107 lines (79 loc) · 4.35 KB
title IDENT_INCR (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
IDENT_INCR
IDENT_INCR_TSQL
dev_langs
TSQL
helpviewer_keywords
incremental values [SQL Server]
IDENT_INCR function
identity columns [SQL Server], IDENT_INCR function
ms.assetid e13b491f-4f1f-4cb6-8b63-5084120f98cf
caps.latest.revision 39
author BYHAM
ms.author rickbyh
manager jhubbard

IDENT_INCR (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx_md]

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.

Topic link icon 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, a variable, a function, or a column name. table_or_view is char, nchar, varchar, or nvarchar.

Return Types

numeric

Exceptions

Returns NULL on error or if a caller does not have permission to view the object.

In [!INCLUDEssNoVersion], 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.

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 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 [!INCLUDEssSampleDBnormal] 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

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)