Skip to main content
Share Your Experience: Take the 2024 Developer Survey
28 votes

Guid vs INT - Which is better as a primary key?

Using auto increment IDs might leak information about your business activity. If you are running a shop and uses order_id to publicly identify a purchase, then anybody can find out your monthly number ...
golopot's user avatar
  • 381
23 votes
Accepted

What is the data type of the ‘ctid’ system column in Postgres?

tid See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as tid. A final identifier type used by the system is ...
Basil Bourque's user avatar
15 votes

How Uniqueidentifier in SQL Server is always a unique value globally?

Problem #1 As per Microsoft documentation on UniqueIdentifier, This value is always a unique globally because it's based on network clock and CPU clock time... (emphasis added) The main problem ...
Solomon Rutzky's user avatar
9 votes

How Uniqueidentifier in SQL Server is always a unique value globally?

Because a UNIQUEIDENTIFIER column can store whatever UNIQUEIDENTIFIER you put in it. Take this code: CREATE TABLE dbo.Test (MyID UNIQUEIDENTIFIER); INSERT INTO dbo.Test VALUES ('809A6AA3-E6D7-4099-...
Brent Ozar's user avatar
9 votes

Guid vs INT - Which is better as a primary key?

I see a whole lot of typical answers on this thread about Random GUIDs and things like NEWSEQUENTIALID() saving the day. Yes, I do agree GUIDs in any form are quite large but that's really the only ...
Jeff Moden's user avatar
8 votes
Accepted

Should I use Index or Unique Key for a non PK Uniqueidentifier?

Unique keys are indexes. If your values are guaranteed to be unique, this is the best choice. Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates ...
indiri's user avatar
  • 2,211
8 votes

Should I use UUID as well as ID

'Rick James' said in the accepted answer: "Having both a UNIQUE AUTO_INCREMENT and a UNIQUE UUID in the same table is a waste". But this test (I did it on my machine) show different facts. For ...
user2241289's user avatar
8 votes
Accepted

Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?

IDENTITY_INSERT is only used for columns with the IDENTITY property. UNIQUEIDENTIFIER is merely a data type. Your insert statement will work.
Paul White's user avatar
  • 85.8k
7 votes
Accepted

Incorrect uniqueidentifier works without a Primary Key but fails with one

I was able to repro this. Both plans had the row from #temptable driving a nested loops join. When #DatabaseTable has a primary key there is an index to seek into so the inside of the join has a ...
Martin Smith's user avatar
  • 84.8k
6 votes

Securely generate a UNIQUEIDENTIFIER in SQL Server

According to https://blogs.msdn.microsoft.com/sqlprogrammability/2006/03/23/newsequentialid-histrorybenefits-and-implementation/, the NEWID() function just wraps the Windows function CoCreateGuid, ...
Jordan Rieger's user avatar
6 votes
Accepted

Trigger to get next prefixed ID: Is there a better way?

I expect your trigger to throw runtime "Mutating table" error . Unless autonomous transaction is used because you cannot query the table that fires the row-level trigger inside its body. I'd rather ...
a1ex07's user avatar
  • 9,010
5 votes

Should I use Index or Unique Key for a non PK Uniqueidentifier?

A few things here that have not already been mentioned. First, is that a UNIQUE constraint can also be used for Referential Integrity. Per the BOL article on Unique Constraints and Check Constraints:...
John Eisbrener's user avatar
5 votes
Accepted

Make a non-ordinal numeric ID in PostgreSQL

but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind Aside from the fact that I think you are giving yourself a false sense of security, you can do what you ...
Jack Douglas's user avatar
5 votes

Guid vs INT - Which is better as a primary key?

The answer by rmirabelle is what I do. However, for larger-scale projects, there is an ultimate design, where both can be used: Use: a Key Mapping Table The TableA.ID is used locally in your database ...
Kind Contributor's user avatar
5 votes

alter primary id field to uniqueidentifier GUID as default in sql server

every tables of my database has id primary key column and I want to change them to nvarchar(36) with default newID() No. You do not want to make that change as no good can come of it. You currently ...
Solomon Rutzky's user avatar
5 votes
Accepted

Why does querying for records that violate a foreign key return incorrect results?

This is a fairly active database  If you can restore a static backup somewhere, and do analysis on that unchanging copy, I think you'll find that the weird behavior you're seeing goes away. Read ...
AMtwo's user avatar
  • 16.2k
4 votes
Accepted

Query on char with selector with N prefix is very slow

It's bad enough that they are using a GUID as a Clustered PK, but they had to make it worse by making it a string and then doing a case-insensitive, locale-aware comparison? Amazing! Well, if you see ...
Solomon Rutzky's user avatar
4 votes

If PK is Non-Clustered index, how to identify clustered index candidate?

that GUID primary key should be Non Clustered always This is not necessarily true. Indexing over effectively random data, which most GUID/UUID values are, can lead to excess page splitting and ...
David Spillett's user avatar
3 votes
Accepted

Performance of using a non-clustered index as primary key

Here is what I have seen based on several years of working with GUIDs as clustered, and nonclustered primary keys...there is no one correct answer here; the big thing really comes down to the access ...
Nic's user avatar
  • 4,023
3 votes
Accepted

Deleting records in relation with other tables

IMHO the best way is: Do not delete the records. As far as I understand your are talking about accounting and financial information. Usually this information cannot be deleted, at least for a minimum ...
McNets's user avatar
  • 23.8k
3 votes

Is GUID(uniqueidentifier) not suitable for a clustered index?

If there is any possibilty of avoiding GUID as Primary Key / Unique Clustered Index, then take this option! A good (surrogate-) key should be: Narrow (NO) Unique (GUID is) Static (GUID is) Ever ...
CeOnSql's user avatar
  • 156
3 votes

Is GUID(uniqueidentifier) not suitable for a clustered index?

It depends. Using a normal UUID as your clustering key will result in greater fragmentation in the table and so waste disk space and memory. You can mitigate this mostly with NEWSEQUENTIALID if that ...
David Spillett's user avatar
3 votes

Is GUID(uniqueidentifier) not suitable for a clustered index?

It will slow down inserts rather than queries. That's because it is essentially inserting a record somewhere randomly inside the existing records, instead of just whacking it on the end (as it would ...
Cody Konior's user avatar
  • 3,508
3 votes

alter primary id field to uniqueidentifier GUID as default in sql server

There is a big problem that you have not considered: As far as I know you cannot remove IDENTITY from a column. Hence, you can only convert this column to int, bigint, smallint, tinyint, or decimal ...
McNets's user avatar
  • 23.8k
3 votes

SQL Server UniqueIdentifier / GUID internal representation

Here is my explanatory-conclusion from long usage and recent review of numerous standards and reference materials. Given an ASCII Format of: {UInt32a-UInt16b-UInt16c-UInt16d-UInt48e} ASCII: ascii ...
smallscript's user avatar
3 votes

Should I use UUID as well as ID

My article collects several points about why you have to use UUIDs from responsive & fast UI perspective and from high-scale perspective. Extracts: After designing the architecture of dozens of ...
Ivan Borshchov's user avatar
3 votes

Clustered vs Non Clustered Indexes for a Primary Key

Unless you switch to sequential GUID generation, you don't want the GUID as a clustered index key. So your choices are 1) Make the ID column the clustered index key. This will optimize joins and ...
David Browne - Microsoft's user avatar
3 votes
Accepted

Must tuples of a relation and their referents be in a one-to-one relationship?

Does the relational model mandate that the relationship between tuples of a relation and their referents be one-to-one No. The relational model only manages the tuples. The mapping of a tuple to an ...
David Browne - Microsoft's user avatar
2 votes

How to uniquely identify SQL instance

This may not be a complete answer to your question, but at least you will be able to identify that the SQL server has been moved to another host. You can use the date when the SQL Server was installed....
Alexander Suprun's user avatar
2 votes

Deleting records in relation with other tables

In principle, and unless there's good reason for it, I agree with @McNets: don't delete the data, just flag it with a certain status. If you still need to delete the client, and you don't want to ...
joanolo's user avatar
  • 13.5k

Only top scored, non community-wiki answers of a minimum length are eligible