IDENT_INCR (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
Returns the increment value specified when creating a table or view's identity column.
Transact-SQL syntax conventions
Syntax
IDENT_INCR ( 'table_or_view' )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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 AdventureWorks2022 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 AdventureWorks2022 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)
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho