3

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

Improve this question
1

2 Answers 2

Reset to default
8

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.

Improve this answer
6
  • 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
    – Hannah Vernon ♦
    Aug 27, 2014 at 21:57
  • @MaxVernon: Why should I get in that habit?
    – Gabe
    Aug 28, 2014 at 4:51
  • 1
    @Gabe For example, merge requires semicolon usage. See also Aaron's blog.
    – vonPryz
    Aug 28, 2014 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, 2014 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.
    – Hannah Vernon ♦
    Aug 28, 2014 at 13:02
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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