First, if you don't want duplicates in this column, state that explicitly.
ALTER TABLE dbo.whatever ADD CONSTRAINT uq_that_column UNIQUE (that_column);
(Or you may want to make that the primary key, or change the clustered index, or what have you...)
In any case, raising an error when you generate a duplicate is far better than just blindly inserting a duplicate that you'll just have to deal with later.
Next, consider that a SEQUENCE is just a number generator, and by default it has a cache of 50 values. Depending on how your transactions are set up, and what other critical events happen on a server, it is possible that SQL Server can "forget" that it generated certain values for you. Sorry but I do not know exactly what criteria factor into reproducing this bug. The way to get around this (until the bug is solved/explained) is to change the sequence to use NO CYCLE
and NO CACHE
, e.g.:
ALTER SEQUENCE dbo.mysequence NO CYCLE NO CACHE;
Note that NO CACHE
can affect performance and concurrency, but will help eliminate gaps, lost blocks and, who knows, maybe your problem too.
You might also want to verify that you're on the most recent service pack and CU. At this point I recommend SP1 and CU10 with 3437; SP2 is out but there is still a critical issue there with online rebuilds that may affect you.
Best Answer
What is a
Sequence
?MSDN on Sequence definition reference.
Ok, it's a schema bound user defined object. What does SQL say about how many
User Defined Objects
we can have?MSDN on maximum UDF reference.
Summary answer: You can have 2,147,483,647 objects in a database. One of those objects referenced would be
Sequence Objects
whichSequences
fall into that category. This is not explicitly stated, but it can be contrived from the wording "... the sum of the number of all objects in a database cannot exceed 2,147,483,647."