Change IDENTITY column

Question

How I can change identity column?

Example I have table:

CREATE TABLE orders
(
 id int IDENTITY(100,1)
 Name varchar(250) 
)

And I need to change as

CREATE TABLE orders
    (
     id int IDENTITY(1000,1)
     Name varchar(250) 
    )

score 5 · Answer 1 · answered Jul 31 '18 at 11:37

5

Basically you want to change the seed of your identity column. you can do using the dbcc command as follows

DBCC CHECKIDENT('orders' , RESEED , 1000)

answered Jul 31 '18 at 11:37

M.Ali

67,945
13
101
127

Correct answer, note that it updates only the *next seed value*. It does not change the table definition, and it does not update existing rows (in case that would be desired). – Peter B Jul 31 '18 at 11:43
Yeah, I need to update existing rows. How it can be done? – Kirill Rodeonov Jul 31 '18 at 11:44

score 0 · Answer 2 · answered Jul 31 '18 at 12:07

You can do something like this if you want exiting columns to be updated aswell

NB: Your switch table must be exactly like your orders table. Same columns etc and in same order.

CREATE TABLE orders_switch
    (

     Name varchar(250) ,
     id int IDENTITY(1000,1)
    )


    insert into orders_switch
    select [Name] from orders



truncate table dbo.orders   alter table [dbo].[orders_switch]      switch to   [dbo].[orders] 

declare @max int 
set @max = (select MAX(id) +1 from dbo.orders)
DBCC CHECKIDENT('orders' , RESEED , @max )

Change IDENTITY column

2 Answers2