Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2012 and I would like to keep my pre-existing data but to cause the ID (identity) column to skip by 2000 rows.

Why is this happening? I have a scenario where I may have transactions happening on a standby server which I will need to copy to current server. I want the IDs to match. Therefore I do not want any new transactions on this server that would have the same ID as my standby server.

Hopefully this makes sense!! Thank you

share|improve this question
2  
For the future, this situation is why GUIDs where invented: stackoverflow.com/questions/371762/… –  Chris Lively Aug 27 at 23:41

2 Answers 2

up vote 6 down vote accepted

You could do this by running something like

declare @newid int;

select @newid = IDENT_CURRENT('YourTable') + 2000;

dbcc checkident('YourTable', reseed, @newid);

This would reseed your table identity to be 2000 higher than it currently is and seems like it should resolve what you are looking to do.

share|improve this answer
1  
+1; I may be preaching to the choir here, but you should get in the habit of appending a ; to every statement in T-SQL, and you should add the schema whenever referencing an object, such as dbo.YourTable –  Max Vernon Aug 27 at 21:57
    
@MaxVernon: Why should I get in that habit? –  Gabe Aug 28 at 4:51
1  
@Gabe For example, merge requires semicolon usage. See also Aaron's blog. –  vonPryz Aug 28 at 6:28
1  
There are other statements that need the semi colon on the previous statement too, otherwise the syntax is ambiguous. Using WITH to define CTEs for instance, without the semi-colon the interpreter can't tell if the WITH is intended to be a hint to the previous query or something to affect the next one (it could probably work it out reliably with some syntax scanning heuristic, but it doesn't and keeping your syntax unambiguous is better for human readers too). –  David Spillett Aug 28 at 10:37
    
Specifying the schema is not a huge problem until you start using more than just dbo. Still doesn't hurt to be direct about your intention, and will prevent hard-to-troubleshoot problems in future. –  Max Vernon Aug 28 at 13:02

One thing to remember is that an ID does not have to be generated by the IDENTITY property.

Using IDENTITY has challenges, since as soon as rows are moved from the standby server to the current server (using IDENTITY_INSERT of course) with an ID value higher than the current identity value it becomes the new current identity value. Which can complicate matters between the two servers.

For other issues see: http://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx

You might find a sequence generator with NOCACHE or some other mechanism to generate IDs on each machine gives you more control. For example, you can set ID ranges that can last for a long time for each server. For example:

  • Server1 ID's from 1000000 to 1999999
  • Server2 ID's from 2000000 to 2999999

Or you might suffix the numbers so that all IDs from Server1 end with a 1 and all IDs from Server2 end with a 2.

Or anything else that helps you avoid or limit the need for juggling values constantly or keeping the two machines aware of each other's state.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.