Duncan Pryde (6/28/2010)
Changing the subject somewhat...
Although I knew that you can have negative seeds and increments for the IDENTITY property, I've never actually used them myself.
I'd therefore be interested to hear if anyone else had used them, and in what sort of situations?
One possible use of negative seed and positive increment is to increase the number of values before you run out. With data type int, the maximum value is 2 billion and a bit. If you expect that, during the lifetime of a database, there will be about 2 billion inserts, using int with the default seed and increment is a bit of a gamble. But if you use -2,147,483,648 as the seed and the default increment of 1, you get a total of over 4 billion insert attempts before you get an overflow error, and you still need only 4 bytes, instead of the 8 bytes you'd use if you choose bigint instead.
Another possible use is in replicated architectures. If new rows are inserted at two locations, you need to ensure that there are no duplicate keys generated. One way is to use even numbers for one location and odd numbers for the other (IDENTITY (2,2) and IDENTITY(1,2)) - the benefit of this system is that you can easily expand it to more nodes. But if you're sure there will always be two nodes, using positive numbers (IDENTITY(1,1)) at one location and negatives (IDENTITY(-1,-1)) at another is also a nice possibility.