Is NEWSEQUENTIALID unique within a database for Many tables?
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
sql-server database-design sql-server-2016 performance-tuning
add a comment |
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
sql-server database-design sql-server-2016 performance-tuning
add a comment |
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
sql-server database-design sql-server-2016 performance-tuning
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
sql-server database-design sql-server-2016 performance-tuning
sql-server database-design sql-server-2016 performance-tuning
asked Dec 12 '18 at 15:49
user162241
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 at 17:53
add a comment |
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.
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
answered Dec 12 '18 at 15:57
Zane
2,64321740
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224783%2fis-newsequentialid-unique-within-a-database-for-many-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 at 17:53
add a comment |
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.
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 at 17:53
add a comment |
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.
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
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.
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
answered Dec 12 '18 at 15:54
George.Palacios
2,178824
2,178824
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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 at 17:53
add a comment |
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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 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
Dec 12 '18 at 17:53
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
Dec 12 '18 at 16:04
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
Dec 12 '18 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
Dec 12 '18 at 16:14
@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
Dec 12 '18 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
Dec 12 '18 at 16:17
@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
Dec 12 '18 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
Dec 12 '18 at 17:53
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
Dec 12 '18 at 17:53
add a comment |
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.
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
answered Dec 12 '18 at 15:57
Zane
2,64321740
add a comment |
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.
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
answered Dec 12 '18 at 15:57
Zane
2,64321740
add a comment |
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.
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
answered Dec 12 '18 at 15:57
Zane
2,64321740
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.
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
answered Dec 12 '18 at 15:57
Zane
2,64321740
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
edited Dec 12 '18 at 17:18
Tom V
13.8k74676
13.8k74676
answered Dec 12 '18 at 15:57
Zane
2,64321740
answered Dec 12 '18 at 15:57
Zane
2,64321740
answered Dec 12 '18 at 15:57
Zane
2,64321740
2,64321740
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224783%2fis-newsequentialid-unique-within-a-database-for-many-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
