T-sql update identity column value

Search related threads. Remove From My Forums. Answered by:. Archived Forums. Sign in to vote. Hi All, I have one table in production with identity column and there are some duplicate id's are inserted manually. Please help me on this. Regards, Vaishu. Thursday, May 23, PM. Checks the current identity value for the specified table in SQL Server The table specified must contain an identity column.

Table names must follow the rules for identifiers. Two or three part names must be delimited, such as 'Person. AddressType' or [Person. The specific corrections made to the current identity value depend on the parameter specifications. The seed value is the value inserted into an identity column for the first row loaded into the table.

All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view. Change the original seed value specified for an identity column when the table or view was created. To change the original seed value and reseed any existing rows, drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values.

The order in which the rows are updated isn't guaranteed. Setting the seed or increment to a value other than 1 results in the following error: The use of seed and increment values other than 1 is not supported with memory optimized tables.

The following example shows generic syntax for finding gaps in identity values when data is removed. The first part of the following Transact-SQL script is designed for illustration only. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode.

Is this page helpful? Please rate your experience Yes No. Any additional feedback? Note In Azure Synapse Analytics values for identity are not incremental due to the distributed architecture of the data warehouse.