Sql-server – Large Identifier Uniqueness Across Table

sql serveruniqueidentifier

We use NEWSEQUENTIALID to generate uniqueidentifier as table ID.
Issue is raised after server reset as the seed of it is modified and sequentiality breaks.

Is there another suggested solution to achieve large identifier uniqueness for a table?

Thank you

Best Answer

Identity values, sequence values, and sequential uniqueidentifier values are never guaranteed to be generated without gaps.

Having said that, if you're using the NEWSEQUENTIALID() (or IDENTITY, or even a SEQUENCE) for a primary key column, why do you care if the values generated are sequential. As long as they are unique, and they are guaranteed to be, what's the problem with gaps between those values?

If you're using that value in some way to confirm correct process, like an invoice number, where you must guarantee each number is sequential, then you'd probably want to use a SEQUENCE that uses no caching.

CREATE SEQUENCE dbo.my_sequence
AS int
START WITH -2147483647
INCREMENT BY 1
NO CYCLE
NO CACHE;

The NO CACHE directive instructs SQL Server to generate numbers from the sequence one-at-a-time, which results in lower performance, but prevents the sequence from missing numbers due to server shutdown, etc.

Since my sequence definition above uses an int data type, and starts with -2147483647, it will generate 4,294,967,296 unique numbers before an error is generated by the NO CYCLE property. If you remove the NO CYCLE property, the sequence will reset to -2147483647 once it hits 2147483648. It that is not a large enough numeric range, you could always use a bigint as the datatype.

Related Question