How to generate globally unique ids for different tables of the same database?
In a production system on SQL Server all ids(mostly PKs) in all tables are generated automatically and I am informed that they are unique globally, I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.
sql-server unique-constraint
add a comment |
In a production system on SQL Server all ids(mostly PKs) in all tables are generated automatically and I am informed that they are unique globally, I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.
sql-server unique-constraint
2
NEWID()
has this functionality. Please check this answer stackoverflow.com/a/28218084/4608204
– EzLo
13 hours ago
add a comment |
In a production system on SQL Server all ids(mostly PKs) in all tables are generated automatically and I am informed that they are unique globally, I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.
sql-server unique-constraint
In a production system on SQL Server all ids(mostly PKs) in all tables are generated automatically and I am informed that they are unique globally, I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.
sql-server unique-constraint
sql-server unique-constraint
edited 12 hours ago
ElGrig
asked 13 hours ago
ElGrigElGrig
68014
68014
2
NEWID()
has this functionality. Please check this answer stackoverflow.com/a/28218084/4608204
– EzLo
13 hours ago
add a comment |
2
NEWID()
has this functionality. Please check this answer stackoverflow.com/a/28218084/4608204
– EzLo
13 hours ago
2
2
NEWID()
has this functionality. Please check this answer stackoverflow.com/a/28218084/4608204– EzLo
13 hours ago
NEWID()
has this functionality. Please check this answer stackoverflow.com/a/28218084/4608204– EzLo
13 hours ago
add a comment |
3 Answers
3
active
oldest
votes
Back in the day we had an ID
table. Single column, single row with an int
value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.
Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe.
It is just possible the application, not the database, assigns and controls these unique numbers. The code would produce an ID value before the INSERT is submitted to the DB. It would act just like the DB Sequence but be implemented in a different architectural tier.
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
1
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
5
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
add a comment |
For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY
option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
). You can of course use a larger (BIGINT
) if the table might need more than 2,147,483,647 rows.
SQL Server has the option of defining your own sequence which can be shared between multiple tables, potentially all of them. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql for details. You then define each ID column as ID INTEGER DEFAULT NEXT VALUE FOR The_sequence_You_Defined PRIMARY KEY
. There are some things to be aware of here though. Unlike with IDENTITY
you are not blocked from dropping in any old value (that isn't already present) as the sequence value is applied by the default only if one is not explicitly given, which could be problematical. Also, using a sequence performs a little more slowly and can become a bottleneck as all tables rely on the same object, though both of these issues are only a concern if your database sees a lot of insert activity in a short space of time. NEXT VALUE FOR The_sequence_You_Defined
can be used elsewhere too (i.e. SET @someVariable = NEXT VALUE FOR The_sequence_You_Defined;
) which means that if you need IDs to be generated elsewhere in your application logic you can have it done this way (in fact I've seen this used even for a single identity, not just sharing a sequence between multiple objects).
A more hacky approach could be to use a BIGINT
for each identity column and start each at a different multiple of (for example) 4,000,000,000. This will work in other DBs and avoids the bottleneck issue, but does double the size of your key and could give you a maintenance nightmare if you accidentally define two tables with IDs starting at the same point. You may wish to add check constraints to make sure an identity value defined this way can't overflow into another value's number space, which adds back in some performance concern.
If you don't mind the larger key, then UUIDs are useful and have the added advantage of being unique between databases (all databases, as the name suggests) not just between tables in one database. As with a sequence these are applied with a default constraint, i.e. ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID()
. These are 128-bit values though, twice the size of BITINT
and four times the size of a "standard" 32-bit INTEGER
. If you are concerned about the potential for extra fragmentation caused by the randomness of v4 UUIDs you can use NEWSEQUENTIALID()
instead of NEWID()
which should still be unique enough (the chance of a collision in the lifetime of this galaxy is vanishingly small).
add a comment |
First of all, I have to mention that I did not work with SQL Server, so I cannot point out some specific features.
I have two concepts of how this can be done on my mind:
- One sequence to rule them all: This concept is easy as it sounds. You have one sequence which is responsible for generating IDs for every row inserted to any table. In my last job, we were using this concept. Implementation depends on a lot of circumstances, so I will let you decide. But one way is to have some stored procedure which will retrieve the next value of the sequence before any insert.
- Timestamp: You can somehow incorporate timestamps into your IDs
In the SQL Server world you can refer to this:
NEWID() documentation - newid is compliant with RFC4122
New contributor
2
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.NEWSEQUENTIALID()
in sql server utilizes this, for example.
– dvlsg
1 hour ago
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%2f232120%2fhow-to-generate-globally-unique-ids-for-different-tables-of-the-same-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Back in the day we had an ID
table. Single column, single row with an int
value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.
Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe.
It is just possible the application, not the database, assigns and controls these unique numbers. The code would produce an ID value before the INSERT is submitted to the DB. It would act just like the DB Sequence but be implemented in a different architectural tier.
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
1
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
5
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
add a comment |
Back in the day we had an ID
table. Single column, single row with an int
value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.
Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe.
It is just possible the application, not the database, assigns and controls these unique numbers. The code would produce an ID value before the INSERT is submitted to the DB. It would act just like the DB Sequence but be implemented in a different architectural tier.
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
1
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
5
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
add a comment |
Back in the day we had an ID
table. Single column, single row with an int
value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.
Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe.
It is just possible the application, not the database, assigns and controls these unique numbers. The code would produce an ID value before the INSERT is submitted to the DB. It would act just like the DB Sequence but be implemented in a different architectural tier.
Back in the day we had an ID
table. Single column, single row with an int
value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.
Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe.
It is just possible the application, not the database, assigns and controls these unique numbers. The code would produce an ID value before the INSERT is submitted to the DB. It would act just like the DB Sequence but be implemented in a different architectural tier.
answered 12 hours ago
Michael GreenMichael Green
15k83061
15k83061
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
1
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
5
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
add a comment |
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
1
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
5
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
Thanks, it helped a lot. One more question, please @MichaelGreen. So does this mean that this function of assigning new values is handled by the application? We create a sequence in database and application uses it to generate unique ids for every insert? right? So basically this option is available only with the integration of application?
– ElGrig
12 hours ago
1
1
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
I'm not sure what you're asking. I would use a SQL Server Sequence if I wanted the database tier to take care of making the IDs globally unique. I would use a static method of some utility class if I wanted the application code to take care of it.
– Michael Green
12 hours ago
5
5
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
I would most definitely NOT get the application to call SQL Server to produce a new global value, which the application then formed into an INSERT statement, which was then passed back to the server to process. Too many network trips in that architecture.
– Michael Green
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
Ok, now everything is clear, thanks
– ElGrig
12 hours ago
add a comment |
For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY
option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
). You can of course use a larger (BIGINT
) if the table might need more than 2,147,483,647 rows.
SQL Server has the option of defining your own sequence which can be shared between multiple tables, potentially all of them. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql for details. You then define each ID column as ID INTEGER DEFAULT NEXT VALUE FOR The_sequence_You_Defined PRIMARY KEY
. There are some things to be aware of here though. Unlike with IDENTITY
you are not blocked from dropping in any old value (that isn't already present) as the sequence value is applied by the default only if one is not explicitly given, which could be problematical. Also, using a sequence performs a little more slowly and can become a bottleneck as all tables rely on the same object, though both of these issues are only a concern if your database sees a lot of insert activity in a short space of time. NEXT VALUE FOR The_sequence_You_Defined
can be used elsewhere too (i.e. SET @someVariable = NEXT VALUE FOR The_sequence_You_Defined;
) which means that if you need IDs to be generated elsewhere in your application logic you can have it done this way (in fact I've seen this used even for a single identity, not just sharing a sequence between multiple objects).
A more hacky approach could be to use a BIGINT
for each identity column and start each at a different multiple of (for example) 4,000,000,000. This will work in other DBs and avoids the bottleneck issue, but does double the size of your key and could give you a maintenance nightmare if you accidentally define two tables with IDs starting at the same point. You may wish to add check constraints to make sure an identity value defined this way can't overflow into another value's number space, which adds back in some performance concern.
If you don't mind the larger key, then UUIDs are useful and have the added advantage of being unique between databases (all databases, as the name suggests) not just between tables in one database. As with a sequence these are applied with a default constraint, i.e. ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID()
. These are 128-bit values though, twice the size of BITINT
and four times the size of a "standard" 32-bit INTEGER
. If you are concerned about the potential for extra fragmentation caused by the randomness of v4 UUIDs you can use NEWSEQUENTIALID()
instead of NEWID()
which should still be unique enough (the chance of a collision in the lifetime of this galaxy is vanishingly small).
add a comment |
For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY
option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
). You can of course use a larger (BIGINT
) if the table might need more than 2,147,483,647 rows.
SQL Server has the option of defining your own sequence which can be shared between multiple tables, potentially all of them. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql for details. You then define each ID column as ID INTEGER DEFAULT NEXT VALUE FOR The_sequence_You_Defined PRIMARY KEY
. There are some things to be aware of here though. Unlike with IDENTITY
you are not blocked from dropping in any old value (that isn't already present) as the sequence value is applied by the default only if one is not explicitly given, which could be problematical. Also, using a sequence performs a little more slowly and can become a bottleneck as all tables rely on the same object, though both of these issues are only a concern if your database sees a lot of insert activity in a short space of time. NEXT VALUE FOR The_sequence_You_Defined
can be used elsewhere too (i.e. SET @someVariable = NEXT VALUE FOR The_sequence_You_Defined;
) which means that if you need IDs to be generated elsewhere in your application logic you can have it done this way (in fact I've seen this used even for a single identity, not just sharing a sequence between multiple objects).
A more hacky approach could be to use a BIGINT
for each identity column and start each at a different multiple of (for example) 4,000,000,000. This will work in other DBs and avoids the bottleneck issue, but does double the size of your key and could give you a maintenance nightmare if you accidentally define two tables with IDs starting at the same point. You may wish to add check constraints to make sure an identity value defined this way can't overflow into another value's number space, which adds back in some performance concern.
If you don't mind the larger key, then UUIDs are useful and have the added advantage of being unique between databases (all databases, as the name suggests) not just between tables in one database. As with a sequence these are applied with a default constraint, i.e. ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID()
. These are 128-bit values though, twice the size of BITINT
and four times the size of a "standard" 32-bit INTEGER
. If you are concerned about the potential for extra fragmentation caused by the randomness of v4 UUIDs you can use NEWSEQUENTIALID()
instead of NEWID()
which should still be unique enough (the chance of a collision in the lifetime of this galaxy is vanishingly small).
add a comment |
For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY
option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
). You can of course use a larger (BIGINT
) if the table might need more than 2,147,483,647 rows.
SQL Server has the option of defining your own sequence which can be shared between multiple tables, potentially all of them. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql for details. You then define each ID column as ID INTEGER DEFAULT NEXT VALUE FOR The_sequence_You_Defined PRIMARY KEY
. There are some things to be aware of here though. Unlike with IDENTITY
you are not blocked from dropping in any old value (that isn't already present) as the sequence value is applied by the default only if one is not explicitly given, which could be problematical. Also, using a sequence performs a little more slowly and can become a bottleneck as all tables rely on the same object, though both of these issues are only a concern if your database sees a lot of insert activity in a short space of time. NEXT VALUE FOR The_sequence_You_Defined
can be used elsewhere too (i.e. SET @someVariable = NEXT VALUE FOR The_sequence_You_Defined;
) which means that if you need IDs to be generated elsewhere in your application logic you can have it done this way (in fact I've seen this used even for a single identity, not just sharing a sequence between multiple objects).
A more hacky approach could be to use a BIGINT
for each identity column and start each at a different multiple of (for example) 4,000,000,000. This will work in other DBs and avoids the bottleneck issue, but does double the size of your key and could give you a maintenance nightmare if you accidentally define two tables with IDs starting at the same point. You may wish to add check constraints to make sure an identity value defined this way can't overflow into another value's number space, which adds back in some performance concern.
If you don't mind the larger key, then UUIDs are useful and have the added advantage of being unique between databases (all databases, as the name suggests) not just between tables in one database. As with a sequence these are applied with a default constraint, i.e. ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID()
. These are 128-bit values though, twice the size of BITINT
and four times the size of a "standard" 32-bit INTEGER
. If you are concerned about the potential for extra fragmentation caused by the randomness of v4 UUIDs you can use NEWSEQUENTIALID()
instead of NEWID()
which should still be unique enough (the chance of a collision in the lifetime of this galaxy is vanishingly small).
For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY
option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
). You can of course use a larger (BIGINT
) if the table might need more than 2,147,483,647 rows.
SQL Server has the option of defining your own sequence which can be shared between multiple tables, potentially all of them. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql for details. You then define each ID column as ID INTEGER DEFAULT NEXT VALUE FOR The_sequence_You_Defined PRIMARY KEY
. There are some things to be aware of here though. Unlike with IDENTITY
you are not blocked from dropping in any old value (that isn't already present) as the sequence value is applied by the default only if one is not explicitly given, which could be problematical. Also, using a sequence performs a little more slowly and can become a bottleneck as all tables rely on the same object, though both of these issues are only a concern if your database sees a lot of insert activity in a short space of time. NEXT VALUE FOR The_sequence_You_Defined
can be used elsewhere too (i.e. SET @someVariable = NEXT VALUE FOR The_sequence_You_Defined;
) which means that if you need IDs to be generated elsewhere in your application logic you can have it done this way (in fact I've seen this used even for a single identity, not just sharing a sequence between multiple objects).
A more hacky approach could be to use a BIGINT
for each identity column and start each at a different multiple of (for example) 4,000,000,000. This will work in other DBs and avoids the bottleneck issue, but does double the size of your key and could give you a maintenance nightmare if you accidentally define two tables with IDs starting at the same point. You may wish to add check constraints to make sure an identity value defined this way can't overflow into another value's number space, which adds back in some performance concern.
If you don't mind the larger key, then UUIDs are useful and have the added advantage of being unique between databases (all databases, as the name suggests) not just between tables in one database. As with a sequence these are applied with a default constraint, i.e. ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID()
. These are 128-bit values though, twice the size of BITINT
and four times the size of a "standard" 32-bit INTEGER
. If you are concerned about the potential for extra fragmentation caused by the randomness of v4 UUIDs you can use NEWSEQUENTIALID()
instead of NEWID()
which should still be unique enough (the chance of a collision in the lifetime of this galaxy is vanishingly small).
answered 12 hours ago
David SpillettDavid Spillett
22.9k23267
22.9k23267
add a comment |
add a comment |
First of all, I have to mention that I did not work with SQL Server, so I cannot point out some specific features.
I have two concepts of how this can be done on my mind:
- One sequence to rule them all: This concept is easy as it sounds. You have one sequence which is responsible for generating IDs for every row inserted to any table. In my last job, we were using this concept. Implementation depends on a lot of circumstances, so I will let you decide. But one way is to have some stored procedure which will retrieve the next value of the sequence before any insert.
- Timestamp: You can somehow incorporate timestamps into your IDs
In the SQL Server world you can refer to this:
NEWID() documentation - newid is compliant with RFC4122
New contributor
2
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.NEWSEQUENTIALID()
in sql server utilizes this, for example.
– dvlsg
1 hour ago
add a comment |
First of all, I have to mention that I did not work with SQL Server, so I cannot point out some specific features.
I have two concepts of how this can be done on my mind:
- One sequence to rule them all: This concept is easy as it sounds. You have one sequence which is responsible for generating IDs for every row inserted to any table. In my last job, we were using this concept. Implementation depends on a lot of circumstances, so I will let you decide. But one way is to have some stored procedure which will retrieve the next value of the sequence before any insert.
- Timestamp: You can somehow incorporate timestamps into your IDs
In the SQL Server world you can refer to this:
NEWID() documentation - newid is compliant with RFC4122
New contributor
2
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.NEWSEQUENTIALID()
in sql server utilizes this, for example.
– dvlsg
1 hour ago
add a comment |
First of all, I have to mention that I did not work with SQL Server, so I cannot point out some specific features.
I have two concepts of how this can be done on my mind:
- One sequence to rule them all: This concept is easy as it sounds. You have one sequence which is responsible for generating IDs for every row inserted to any table. In my last job, we were using this concept. Implementation depends on a lot of circumstances, so I will let you decide. But one way is to have some stored procedure which will retrieve the next value of the sequence before any insert.
- Timestamp: You can somehow incorporate timestamps into your IDs
In the SQL Server world you can refer to this:
NEWID() documentation - newid is compliant with RFC4122
New contributor
First of all, I have to mention that I did not work with SQL Server, so I cannot point out some specific features.
I have two concepts of how this can be done on my mind:
- One sequence to rule them all: This concept is easy as it sounds. You have one sequence which is responsible for generating IDs for every row inserted to any table. In my last job, we were using this concept. Implementation depends on a lot of circumstances, so I will let you decide. But one way is to have some stored procedure which will retrieve the next value of the sequence before any insert.
- Timestamp: You can somehow incorporate timestamps into your IDs
In the SQL Server world you can refer to this:
NEWID() documentation - newid is compliant with RFC4122
New contributor
New contributor
answered 12 hours ago
Tomáš Na'vi KoválikTomáš Na'vi Koválik
312
312
New contributor
New contributor
2
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.NEWSEQUENTIALID()
in sql server utilizes this, for example.
– dvlsg
1 hour ago
add a comment |
2
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.NEWSEQUENTIALID()
in sql server utilizes this, for example.
– dvlsg
1 hour ago
2
2
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
How would a timestamp prevent duplicate ids? Events can happen simultaneously.
– ddunn801
9 hours ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.
NEWSEQUENTIALID()
in sql server utilizes this, for example.– dvlsg
1 hour ago
Timestamps don't prevent them, but timestamp + other uniqueness is a fairly common tactic for building unique identifiers.
NEWSEQUENTIALID()
in sql server utilizes this, for example.– dvlsg
1 hour ago
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.
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%2f232120%2fhow-to-generate-globally-unique-ids-for-different-tables-of-the-same-database%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
2
NEWID()
has this functionality. Please check this answer stackoverflow.com/a/28218084/4608204– EzLo
13 hours ago