3

Is NEWSEQUENTIALID unique within a database? We plan many tables in a database with NEWSEQUENTIALID, inserting simultaneously and want to ensure every guid will be unique, regardless if they are in different tables.

Regular NEWID() will guarantee uniqueness in a database, and chances of having duplicate is chance of being struck by asteroid/lightning, however they do not alleviate the fragmentation issue.

This article talks about Mac address, however I want to specify question for database specific above. https://stackoverflow.com/questions/28404964/is-uniqueidentifier-unique-across-databases

Improve this question

2 Answers 2

Reset to default
5

According to Microsoft Docs the value is unique on a per computer basis, and also on multiple computers as long as the computer in question has a network card (assumedly the generation algorithm uses some values taken from the network card to generate the GUID)

Each GUID generated by using NEWSEQUENTIALID is unique on that computer. GUIDs generated by using NEWSEQUENTIALID are unique across multiple computers only if the source computer has a network card.

Other than this though, there do seem to be some caveats around that uniqueness:

The UuidCreateSequential function has hardware dependencies. On SQL Server, clusters of sequential values can develop when databases (such as contained databases) are moved to other computers. When using Always On and on SQL Database, clusters of sequential values can develop if the database fails over to a different computer.

And on the page for UuidCreateSequential we can also see that the function is guaranteed to be unique across computers if the network card is present

Computers with ethernet/token ring addresses generate UUIDs that are guaranteed to be globally unique.

Improve this answer
4
  • ok, we are not using APIs yet (so UuidCreateSequential will not apply to us), this is for Kimball data warehouse, so I will use NewSequentialId for all tables within database, thanks again, wonder how it will work when we move the Datawarehouse to the cloud, I will stick with Newsequential id for on premise database, Thanks
    – user162241
    Commented Dec 12, 2018 at 16:04
  • @knightbob472 if you look at the first link, you'll see that NewSequentialID is a wrapper for UuidCreateSequential, so you do need to read and understand both.
    – George.Palacios
    Commented Dec 12, 2018 at 16:14
  • @knightbob472 if this answer resolves your question it's always helpful to mark it as the answer. It gives credit to the answer and informs other readers that this is a good place to look.
    – Zane
    Commented Dec 12, 2018 at 16:17
  • Thanks, I usually let it sit for couple days unanswered, so it gains attention, and person can rack up more points for providing answer, it'll be accepted in few days
    – user162241
    Commented Dec 12, 2018 at 17:53
5

Acording to the documentation from Microsoft.

NEWSEQUENTIALID

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique

I recommend using NEWSEQUENTIALID over NEWID() for any case that that you aren't relying on the built in randomness, mainly due to the fragmentation issues associated with random GUIDs. There is a fantastic article by Kimberly Tripp on some of the pitfalls of using NEWID() on a Primary key.

There are a few situations where NEWSEQUENTIALID may not guarantee uniqueness as are listed in the documentation.

The UuidCreateSequential function has hardware dependencies. On SQL Server, clusters of sequential values can develop when databases (such as contained databases) are moved to other computers. When using Always On and on SQL Database, clusters of sequential values can develop if the database fails over to a different computer.

However judging by the phrasing of your question it seems like this will be contained on a single database server.

It is also worth noting(not that it applies here) that NEWSEQUENTIALID can be predicted so if privacy of that GUID is a concern this could also be a problem.

Improve this answer
1
  • "pitfalls of using NEWID() on a CLUSTERED Primary key"
    – Radu Gheorghiu
    Commented Oct 16, 2019 at 12:15

Your Answer

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