DB agnostic sequential UUID - what column type?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I'm trying to follow latest somewhat agreed-upon best-practice for Primary Keys. From what I gather it goes like this:
- Use a UUID for primary key. Only in very, very rare cases is there a
suitable natural key and the recommendation can be deviated from. - Let the application, not the database, generate the value.
- Use a sequential UUID (which - strictly speaking - means it is no longer
a UUID, but for the sake of the argument we'll continue to use the 'UUID'
phrase)
The application is meant to be fairly DB agnostic. At least cover those databases listed below. My problem is that I cannot figure out what is a suitable column type (for each database) given that the column needs to store an UUID-like value..
Requirements for column type:
- Must hold a 16 byte value (128 bit)
- Must be acceptable by the
database for use as a PK and must not have significantly adverse query
performance compared to say a BIGINT. - The database must sort/compare the value same way as Java would do,
meaning entirely in big-endian format (irrespective of database's defined
collation)
As UUID-like values are gaining traction for use as PK in databases you would think that this is something which has already been documented several times. However, I have not been able to find any clear documentation on this.
Below is how far I got. These are the column types which I think will fulfill the requirements.
Oracle: RAW(16)
-- or perhaps BINARY(16) ? Is there any difference?
MySQL: BINARY(16)
MariaDB: BINARY(16)
PostgreSQL: UUID
-- difficult to find documentation on this beast. PostgreSQL doesn't
-- seem to have a fixed-length binary type?
MS SQL Server: BINARY(16)
-- I understand, I need to stay away from 'uniqueidentifier'
-- type due to sorting.
Apache Derby: CHAR(16) FOR BIT DATA
My application doesn't have to support older versions of those databases.
sql-server mysql postgresql oracle uuid
asked 3 mins ago
peterhpeterh
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm trying to follow latest somewhat agreed-upon best-practice for Primary Keys. From what I gather it goes like this:
- Use a UUID for primary key. Only in very, very rare cases is there a
suitable natural key and the recommendation can be deviated from. - Let the application, not the database, generate the value.
- Use a sequential UUID (which - strictly speaking - means it is no longer
a UUID, but for the sake of the argument we'll continue to use the 'UUID'
phrase)
The application is meant to be fairly DB agnostic. At least cover those databases listed below. My problem is that I cannot figure out what is a suitable column type (for each database) given that the column needs to store an UUID-like value..
Requirements for column type:
- Must hold a 16 byte value (128 bit)
- Must be acceptable by the
database for use as a PK and must not have significantly adverse query
performance compared to say a BIGINT. - The database must sort/compare the value same way as Java would do,
meaning entirely in big-endian format (irrespective of database's defined
collation)
As UUID-like values are gaining traction for use as PK in databases you would think that this is something which has already been documented several times. However, I have not been able to find any clear documentation on this.
Below is how far I got. These are the column types which I think will fulfill the requirements.
Oracle: RAW(16)
-- or perhaps BINARY(16) ? Is there any difference?
MySQL: BINARY(16)
MariaDB: BINARY(16)
PostgreSQL: UUID
-- difficult to find documentation on this beast. PostgreSQL doesn't
-- seem to have a fixed-length binary type?
MS SQL Server: BINARY(16)
-- I understand, I need to stay away from 'uniqueidentifier'
-- type due to sorting.
Apache Derby: CHAR(16) FOR BIT DATA
My application doesn't have to support older versions of those databases.
sql-server mysql postgresql oracle uuid
asked 3 mins ago
peterhpeterh
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm trying to follow latest somewhat agreed-upon best-practice for Primary Keys. From what I gather it goes like this:
- Use a UUID for primary key. Only in very, very rare cases is there a
suitable natural key and the recommendation can be deviated from. - Let the application, not the database, generate the value.
- Use a sequential UUID (which - strictly speaking - means it is no longer
a UUID, but for the sake of the argument we'll continue to use the 'UUID'
phrase)
The application is meant to be fairly DB agnostic. At least cover those databases listed below. My problem is that I cannot figure out what is a suitable column type (for each database) given that the column needs to store an UUID-like value..
Requirements for column type:
- Must hold a 16 byte value (128 bit)
- Must be acceptable by the
database for use as a PK and must not have significantly adverse query
performance compared to say a BIGINT. - The database must sort/compare the value same way as Java would do,
meaning entirely in big-endian format (irrespective of database's defined
collation)
As UUID-like values are gaining traction for use as PK in databases you would think that this is something which has already been documented several times. However, I have not been able to find any clear documentation on this.
Below is how far I got. These are the column types which I think will fulfill the requirements.
Oracle: RAW(16)
-- or perhaps BINARY(16) ? Is there any difference?
MySQL: BINARY(16)
MariaDB: BINARY(16)
PostgreSQL: UUID
-- difficult to find documentation on this beast. PostgreSQL doesn't
-- seem to have a fixed-length binary type?
MS SQL Server: BINARY(16)
-- I understand, I need to stay away from 'uniqueidentifier'
-- type due to sorting.
Apache Derby: CHAR(16) FOR BIT DATA
My application doesn't have to support older versions of those databases.
sql-server mysql postgresql oracle uuid
asked 3 mins ago
peterhpeterh
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to follow latest somewhat agreed-upon best-practice for Primary Keys. From what I gather it goes like this:
- Use a UUID for primary key. Only in very, very rare cases is there a
suitable natural key and the recommendation can be deviated from. - Let the application, not the database, generate the value.
- Use a sequential UUID (which - strictly speaking - means it is no longer
a UUID, but for the sake of the argument we'll continue to use the 'UUID'
phrase)
The application is meant to be fairly DB agnostic. At least cover those databases listed below. My problem is that I cannot figure out what is a suitable column type (for each database) given that the column needs to store an UUID-like value..
Requirements for column type:
- Must hold a 16 byte value (128 bit)
- Must be acceptable by the
database for use as a PK and must not have significantly adverse query
performance compared to say a BIGINT. - The database must sort/compare the value same way as Java would do,
meaning entirely in big-endian format (irrespective of database's defined
collation)
As UUID-like values are gaining traction for use as PK in databases you would think that this is something which has already been documented several times. However, I have not been able to find any clear documentation on this.
Below is how far I got. These are the column types which I think will fulfill the requirements.
Oracle: RAW(16)
-- or perhaps BINARY(16) ? Is there any difference?
MySQL: BINARY(16)
MariaDB: BINARY(16)
PostgreSQL: UUID
-- difficult to find documentation on this beast. PostgreSQL doesn't
-- seem to have a fixed-length binary type?
MS SQL Server: BINARY(16)
-- I understand, I need to stay away from 'uniqueidentifier'
-- type due to sorting.
Apache Derby: CHAR(16) FOR BIT DATA
My application doesn't have to support older versions of those databases.
sql-server mysql postgresql oracle uuid
sql-server mysql postgresql oracle uuid
asked 3 mins ago
peterhpeterh
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 mins ago
peterhpeterh
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 mins ago
peterhpeterh
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 mins ago
peterhpeterh
101
asked 3 mins ago
peterhpeterh
101
101
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
peterh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
peterh is a new contributor. Be nice, and check out our Code of Conduct.
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%2f234384%2fdb-agnostic-sequential-uuid-what-column-type%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
peterh is a new contributor. Be nice, and check out our Code of Conduct.
peterh is a new contributor. Be nice, and check out our Code of Conduct.
peterh is a new contributor. Be nice, and check out our Code of Conduct.
peterh is a new contributor. Be nice, and check out our Code of Conduct.
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%2f234384%2fdb-agnostic-sequential-uuid-what-column-type%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
