Post reply

How to change the identity increment in table besides thru EM?

  • May 18, 2005 at 5:22 pm

    #164624

    Hi all,

    I have worked on this for long time now, I am sure there has to be a way to do this. How can I change the identity increment value programatically. Thru SQL or something.

    Remember I am not taking about seed.

    I have a table A With Id int identity(1,1) not null. It has over 10 million rows.

    I want to change the identity increment to 10 so column looks like Id int identity(1000001,10) not null.

    I can change the identity seed thru DBCC CHECKIDENT (A, RESEED, 1000001)

    but problem is changing increment.

    I know i can do it from enterprise manager but that just creates temp table and does all these long route and takes forever due to table size. Is there anyplace in database I can just change the value of this? or function? or proc? or dbcc? anything.

    Thanks,

    A~DBA

  • May 18, 2005 at 11:45 pm

    #559775

    you can try creating temp column ,update that column with current identity column data then drop the current identity column and re create it with new increment value and then update it back from temporary column.

    You may have already thought abuot this method, this probably better than EM's way. and won't take that long since you are only playing with the int column.

    I don't see any other means of doing it.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply