January 28, 2009 at 10:20 am
Say I have a tableA with the following columns:
ID (PK, int, identity, not null)
name (varchar(50), not null)
address (varchar(50), not null)
I originally had the identity seed set at 0, and I have 10 rows of columns populated. So, ID columnn has values from 1 to 10. I now want to start the auto increment identity counter to start at 20. Can I reseed the ID identity column in tableA if the table already has data in it? If so, are there any performance ramifications?
Can I simply use this command:
dbcc checkident('tableA',reseed,19)
If so, then would the next row I add have an ID value of 20?
January 28, 2009 at 10:26 am
The answer is yes
CREATE TABLE tmp
(col1 INT IDENTITY,Col2 INT)
INSERT INTO tmp VALUES(1)
SELECT * FROM tmp
INSERT INTO tmp VALUES(1)
DBCC CHECKIDENT(tmp,reseed,19)
SELECT * FROM tmp
the question is why do you want to do that?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 28, 2009 at 10:41 am
Christopher,
The reason for doing this is because we are migrating trouble tickets from an old help desk ticketing system to a new one. In doing so, I am going to pre-populate (insert into/values) over 13,000 old tickets, then perform update/set where the old and new ticket numbers match. However, since we are still going to be live in the old system while running my migration queries, I want to reseed just after migrating 13,000 tickets to allow a buffer for new tickets that are auto generated during the migration period. So, I would reseed to say 13,010 which allows me to have up to 10 new tickets auto generate and be able to correctly map/maintain the old ticket numbers in the new system. It's almost too difficult to explain. Ultimately, what I want to do is make it so all new tickets (in the new tracking system) start at a specific number (say 13,010). All old migrated tickets will have an ID of 13,010 or less.
January 28, 2009 at 2:32 pm
Pat_B (1/28/2009)
Say I have a tableA with the following columns:ID (PK, int, identity, not null)
name (varchar(50), not null)
address (varchar(50), not null)
I originally had the identity seed set at 0, and I have 10 rows of columns populated. So, ID columnn has values from 1 to 10. I now want to start the auto increment identity counter to start at 20. Can I reseed the ID identity column in tableA if the table already has data in it? If so, are there any performance ramifications?
Can I simply use this command:
dbcc checkident('tableA',reseed,19)
If so, then would the next row I add have an ID value of 20?
Why not just make a dummy table somewhere and try it? You might want to take a look at Books Online for what the next value would be after a reseed, as well.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply