Resetting the SQL Server identity column

The SQL Server Identity column is very familiar to you, and here's some knowledge of SQL Server identity column resets, so if you're interested in SQL Server identity columns, you might want to look at them.

1. Delete all values in the table and reset the identity value TRUNCATE table TableName

2. Keep the contents of the table and reset the seed of the AutoNumber column

DBCC checkident (' table_name ', reseed, New_reseed_value)

such as DBCC CHECKIDENT ("Bc_pos", reseed,1), but if there is data in the table, the reset value may be problematic if it is less than the maximum value, then you can use DBCC CHECKIDENT ("Bc_pos", reseed) to automatically reset the value.

3. Whether a table has a SQL Server identity column

You can use the OBJECTPROPERTY function to determine whether a table has an identity (identity) column and uses:

Select objectproperty (object_id (' table name '), ' table has Identity ')

Returns 1 if any, otherwise returns 0

4. Determine if a column is an identity column

You can use the COLUMNPROPERTY function to determine whether a column has an identity property, and the usage

SELECT columnproperty (object_id (' table name '), ' column name ', ' isidentity ')

Returns 1 if the column is a label, otherwise 0

4. Query the column name of a table identity column

There are no ready-made functions in SQL Server to implement this functionality, and the SQL statements implemented are

SELECT column_name from Information_schema.columns

WHERE table_name= ' table name ' and ColumnProperty (

object_id (' table name '), column_name, ' isidentity ') =1

5. References to Identity columns

If the identity column is referenced in an SQL statement, the keyword IDENTITYCOL is used instead

For example, to query for rows with an ID equal to 1 in the previous example,

The following two query statements are equivalent

SELECT * from T_test WHERE identitycol=1

SELECT * from T_test WHERE id=1

6. Get the seed value of the identity column

You can use the function ident_seed, usage:

SELECT ident_seed (' Table name ')

7. Get increment of identity column

You can use the function ident_incr, usage:

SELECT ident_incr (' Table name ')

8. Gets the last generated identity value in the specified table

You can use the function ident_current, usage:

SELECT ident_current (' Table name ')

Note: When the table that contains the identity column has just been created, the value that is obtained by using the Ident_current function is the seed value of the identity column for any insert operation, especially when developing database applications.

Resetting the SQL Server identity column