Как сбросить id в sql
Identity column of a table is a column whose value increases automatically. A user generally cannot insert a value into an identity column. A table can have only one column that is defined with the identity attribute.
Syntax :
Default value of identity is IDENTITY (1,1).
Seed : The seed represents the starting value of an ID and the default value of seed is 1.
Increment : It will represent the incremental value of the ID and the default value of increment is 1.
For Example :
Step 1 : Create a table named school.
Here, the ‘student_id’ column of the table starts from 1 as the default value of seed is 1 and each row is incremented by 1.
Step 2 : Insert some value into a table.
Step 3 : To see the records in the table ‘school’ , we can use the following code:
Output :
| student_id | student_name | marks |
|---|---|---|
| 1 | Sahil | 100 |
| 2 | Raj | 78 |
| 3 | Navneet | 80 |
| 4 | Rahul | 75 |
| 5 | Sudeep | 82 |
| 6 | Azaan | 75 |
Step 4 : Lets delete a record.
Step 5 : To see the records in the table.
Output :
| student_id | student_name | marks |
|---|---|---|
| 1 | Sahil | 100 |
| 2 | Raj | 78 |
| 3 | Navneet | 80 |
| 5 | Sudeep | 82 |
| 6 | Azaan | 75 |
Now, you can see that the student_id column is not in order, So you have to reset the Identity Column.
Reset the Identity Value Using the DBCC CHECKIDENT Method :
Here, to reset the Identity column in SQL Server you can use DBCC CHECKIDENT method.
Syntax :
Note : If we reset the existing records in the table and insert new records, then it will show an error.
- Create a new table as a backup of the main table (i.e. school).
- Delete all the data from the main table.
- And now reset the identity column.
- Re-insert all the data from the backup table to main table.
Step 6 : Create backup table named ‘new_school’ .
Step 7 : Delete all the data from school.
Step 8 : Reset the Identity column.
Step 9 : Re-insert all the data from the backup table to main table.
How to reset identity column values in SQL Server?
Here you will learn how to reset value of identity column in a table.
In SQL Server, an identity column is used to auto-increment a column. It is useful in generating a unique number for primary key columns, where the value is not important as long as it is unique.
The following CREATE TABLE statement declares EmpID as the identity column.
In the above CREATE TABLE SQL statement, EmpID is the IDENTITY column with seed and increment at 1. So, whenever a new row is inserted, the ID will be incremented by 1.
Now, let's insert a row into the new Employee table.
The above statement will insert the following record.
As you can see, the EmpID value is 1 in the first record. If you insert another record then it will be 2 and so on.
Reset IDENTITY column values
For any reason, if an insert fails or is rolled back, the EmpID number that was generated is lost and there will be gaps in the EmpID column. In some cases, this can be ignored and the gaps will not make a difference. But there could be instances when it is necessary to not have gaps in the IDENTITY column. In such cases, you can reset the identity column.
Let's execute the following statement which will raise an error because it tries to enter NULL in the NOT NULL column.
Reset the identity column value in MSSQL
How to reset the identity column value in the SQL server?
To reset the current identity value for the table you should use DBCC CHECKIDENT function. It’s a great way to control the numbers sequence for the identity column.
DBCC CHECKIDENT function is very useful for database maintenance and can be applied to numeric types:
- smallint
- bigint
- tinyint
- numeric
- integer
- decimal
Why should use DBCC CHECKIDENT function and reset an Identity Column?
In most cases we want to set a new identity value, when:
- Numbers sequence should be controlled in general
- Table rows were deleted and numbering should start from the beginning
- Table rows were archived and custom numbering should be set from the initial point
- The identity seed was incorrectly and the issue should be fixed
Learn DBCC CHECKIDENT Syntax
The DBCC CHECKIDENT function syntax looks as follows (MS docs):
Learn DBCC CHECKIDENT Arguments
- new_reseed_value – highest identify value in the table
- RESEED – forces current identity value to change
- NORESEED – returns the current value
Quick Example in 7 simple steps
A quick action plan looks as follows:
- Create table dbo.Logs with an integer identity column
- Insert 10 rows
- Check current identity value
- Delete records from the table
- Reset identity value to 1
- Insert 1 record
- Check identity value – should be equal to 1
In the first step let’s create the dummy table:
Next, let’s insert 10 records:
and display the data:
Now, the identity value should be 10.
Let’s check the current identity value this using the NORESEED argument :
At this point all is good.
Now, Let’s delete all data from the Logs table using the DELETE function:
Now, to reset the value to the initial point (let’ pick 1), just execute the following snippet:
Then we can insert a fresh record and execute the select command to see rows in the table.
Bonus Tip: When you use the TRUNCATE TABLE command to clear your table, with a given example table (Logs) – the column identity value will be automatically reset to 1.
Final thoughts
Resetting column identity value by DBCC CHECKIDENT function is super easy.
As we can see in the example – we’ve set the column identity value back to the initial point and we can start populating it from the beginning.
How to reset an IDENTITY column value In SQL Server?
Learn how to reset an IDENTITY column value In SQL Server.
An IDENTITY column is an auto-incrementing column. This column cannot be updated directly. If we need to reset this column, we have two different options.
- TRUNCATE
- DBCC CHECKIDENT
Let us create a table below to demonstrate the use of both of the above methods.
The current value of the IDENTITY column will not change even if we delete data from the table. That means, if we delete the last row, the value of IDENTITY for the new row inserted will not be 4. It will still be 5.
- TRUNCATE
The TRUNCATE command performs two operations:
a) Delete all the data from a table
b) Reset the IDENTITY column to 0
2. DBCC CHECKIDENT
We can not use TRUNCATE in call the case like it cannot be used with the table with a foreign key. Also, TRUNCATE can only reset the IDENTITY to value 0. If we need to reseed it to any other value, we should use the DBCC CHECKIDENT command.