Identity column

Shubham Srivastava

14y1.9k

Hi,

how can reset the identity column in Sql Server ?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Hi Shubham,

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.

It would be wise to first check what the current identify value is. We can use this command to do so:

DBCC CHECKIDENT ('tablename', NORESEED)

For instance, if I wanted to check the next ID value of my orders table, I could use this command:

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, I can use this command:

DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.

Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn't)

DBCC CHECKIDENT ( 'databasename.dbo.orders',RESEED, 999)

Hope this will help you.

0
Satyapriya Nayak14y ago
Hi Shubham,

Please refer the below link

http://www.codeproject.com/Tips/264381/How-to-RESET-identity-column-in-SQL-Server

Thanks
0
MuthuMari M14y ago
Hi,

Using the DELETE/Truncate statement is not enough. The statement only deletes the data but not reset the identity column.

So how can we do it? First, wen need to delete the data from the table(truncate the table) and after execute the DBCC command with CHECKIDENT switch in order to reset the data
The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.
DBCC CHECKIDENT('Customer', RESEED, 0)

thanks

If this post is useful then mark it as "Accepted Answer"
0
brunda k14y ago
DBCC CHECKIDENT
(
    table_name
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]


http://msdn.microsoft.com/en-us/library/ms176057.aspx
0
NIlesh Patel14y ago
Dear
It can not Possible to Reset Identity Column In Sql Server. But By using 'Truncate Table Tablename ' This Query It can Reset But Data Also Deleted.
0