IDENTITY (Property) (Transact-SQL), Microsoft Docs

Ter this article

Creates an identity katern ter a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

The IDENTITY property is different from the SQL-DMO Identity property that exposes the row identity property of a katern.



Is the value that is used for the very very first row loaded into the table.

Is the incremental value that is added to the identity value of the previous row that wasgoed loaded.

You vereiste specify both the seed and increment or neither. If neither is specified, the default is (1,1).


Identity columns can be used for generating key values. The identity property on a katern assures the following:

Each fresh value is generated based on the current seed &, increment.

Each fresh value for a particular transaction is different from other mededinger transactions on the table.

The identity property on a katern does not ensure the following:

Uniqueness of the value – Uniqueness vereiste be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

Consecutive values within a transaction – A transaction inserting numerous rows is not ensured to get consecutive values for the rows because other mededinger inserts might occur on the table. If values vereiste be consecutive then the transaction should use an sensational lock on the table or use the SERIALIZABLE isolation level.

Consecutive values after server restart or other failures – SQL Server might cache identity values for voorstelling reasons and some of the assigned values can be lost during a database failure or server restart. This can result ter gaps ter the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is flipped back then the consumed identity values are lost and will not be generated again. This can result ter gaps when the subsequent identity values are generated.

Thesis confinements are part of the vormgeving te order to improve spectacle, and because they are acceptable ter many common situations. If you cannot use identity values because of thesis confinements, create a separate table holding a current value and manage access to the table and number assignment with your application.

If a table with an identity katern is published for replication, the identity katern voorwaarde be managed te a way that is suitable for the type of replication used. For more information, see Replicate Identity Columns.

Only one identity katern can be created vanaf table.

Ter memory-optimized tables the seed and increment vereiste be set to 1,1. Setting the seed or increment to a value other than 1 results ter the following error: The use of seed and increment values other than 1 is not supported with memory optimized tables.


A. Using the IDENTITY property with CREATE TABLE

The following example creates a fresh table using the IDENTITY property for an automatically incrementing identification number.

B. Using generic syntax for finding gaps ter identity values

The following example shows generic syntax for finding gaps ter identity values when gegevens is eliminated.

The very first part of the following Transact-SQL script is designed for illustration only. You can run the Transact-SQL script that starts with the comment: — Create the img table .

See Also

The terugkoppeling system for this content will be switching soon. Old comments will not be carried overheen. If content within a comment thread is significant to you, please save a copy. For more information on the upcoming switch, wij invite you to read our blog postbode.

Related movie: Top Four BEST ALTCOIN BUYS – March 2018

Leave a Reply

Your email address will not be published. Required fields are marked *