Increase value of an identity column in SQL Server without dropping and recreating the table

edx
edx
58 Points
19 Posts

In a table 'Customer', I have an Id column, which is an Identity int.
How can I make it so that the next row inserted will get identity 1000(I think, this is called the identity seed) without dropping and recreating the 'Customer' table?
I want to do this because of some uniqueness issue with an external service in the application.

Views: 251
Total Answered: 2
Total Marked As Answer: 1
Posted On: 09-May-2019 05:47

Share:   fb twitter linkedin
Answers
Brian
Brian
Participant
592 Points
76 Posts
         

You can use following Transact-SQL command

DBCC CHECKIDENT
(
  table_name
    [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)

If you pass single parameter as:

DBCC CHECKIDENT
(
   table_name
)

will check the current identity value for the specified table in SQL Server:

Example

DBCC CHECKIDENT(tbl_test)

Output

Checking identity information: current identity value '115', current column value '115'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Following will insert new identity value for the identity column:

DBCC CHECKIDENT
(
  table_name , RESEED, new_reseed_value
)

Example

DBCC CHECKIDENT(tbl_test,reseed,116)

Output

Checking identity information: current identity value '115', current column value '116'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Posted On: 10-May-2019 19:50
beginer
beginer
98 Points
34 Posts
         
DBCC CHECKIDENT (yourtable, reseed, 500)

This will make the next entry 501.

Posted On: 10-May-2019 19:57
 Log In to Chat