What are the best practices for using a GUID as a primary key specifically regarding performance

Question

I have an application that uses GUID as the Primary Key in almost all tables and I have read that there are issues about performance when using GUID as Primary Key  Honestly  I haven t seen any problem  but I m about to start a new application and I still want to use the GUIDs as the Primary Keys  but I was thinking of using a Composite Primary Key  The GUID and maybe another field    I m using a GUID because they are nice and easy to manage when you have different environments such as  production    test  and  dev  databases  and also for migration data between databases   I will use Entity Framework 4 3 and I want to assign the Guid in the application code  before inserting it in the database   i e  I don t want to let SQL generate the Guid    What is the best practice for creating GUID-based Primary Keys  in order to avoid the supposed performance hits associated with this approach

User · Answer

Most of the times it should not be used as the primary key for a table because it really hit the performance of the database  useful links regarding GUID impact on performance and as a primary key    https   www sqlskills com blogs kimberly disk-space-is-cheap   https   www sqlskills com blogs kimberly guids-as-primary-keys-andor-the-clustering-key

User · Answer

This link says it better than I could and helped in my decision making   I usually opt for an int as a primary key  unless I have a specific need not to and I also let SQL server auto-generate maintain this field unless I have some specific reason not to   In reality  performance concerns need to be determined based on your specific app   There are many factors at play here including but not limited to expected db size  proper indexing  efficient querying  and more   Although people may disagree  I think in many scenarios you will not notice a difference with either option and you should choose what is more appropriate for your app and what allows you to develop easier  quicker  and more effectively  If you never complete the app what difference does the rest make      https   web archive org web 20120812080710 http   databases aspfaq com database what-should-i-choose-for-my-primary-key html  P S  I m not sure why you would use a Composite PK or what benefit you believe that would give you

User · Answer

If you use GUID as primary key and create clustered index then I suggest use the default of NEWSEQUENTIALID   value for it

User · Answer

GUIDs may seem to be a natural choice for your primary key - and if you really must  you could probably argue to use it for the PRIMARY KEY of the table  What I d strongly recommend not to do is use the GUID column as the clustering key  which SQL Server does by default  unless you specifically tell it not to   You really need to keep two issues apart    the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table  This can be anything  really - an INT  a GUID  a string - pick what makes most sense for your scenario  the clustering key  the column or columns that define the  clustered index  on the table  - this is a physical storage-related thing  and here  a small  stable  ever-increasing data type is your best pick - INT or BIGINT as your default option     By default  the primary key on a SQL Server table is also used as the clustering key - but that doesn t need to be that way  I ve personally seen massive performance gains when breaking up the previous GUID-based Primary   Clustered Key into two separate key - the primary  logical  key on the GUID  and the clustering  ordering  key on a separate INT IDENTITY 1 1  column    As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn t optimal  since due to its randomness  it will lead to massive page and index fragmentation and to generally bad performance   Yes  I know - there s newsequentialid   in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so   Then there s another issue to consider  the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it s as small as possible  Typically  an INT with 2  billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key  you can save yourself hundreds of megabytes of storage on disk and in server memory    Quick calculation - using INT vs  GUID as Primary and Clustering Key    Base Table with 1 000 000 rows  3 8 MB vs  15 26 MB  6 nonclustered indexes  22 89 MB vs  91 55 MB    TOTAL  25 MB vs  106 MB - and that s just on a single table   Some more food for thought - excellent stuff by Kimberly Tripp - read it  read it again  digest it  It s the SQL Server indexing gospel  really    GUIDs as PRIMARY KEY and or clustered key The clustered index debate continues Ever-increasing clustering key - the Clustered Index Debate          again  Disk space is cheap - that s not the point    PS  of course  if you re dealing with just a few hundred or a few thousand rows - most of these arguments won t really have much of an impact on you  However  if you get into the tens or hundreds of thousands of rows  or you start counting in millions - then those points become very crucial and very important to understand   Update  if you want to have your PKGUID column as your primary key  but not your clustering key   and another column MYINT  INT IDENTITY  as your clustering key - use this   CREATE TABLE dbo MyTable  PKGUID UNIQUEIDENTIFIER NOT NULL   MyINT INT IDENTITY 1 1  NOT NULL        add more columns as needed           ALTER TABLE dbo MyTable ADD CONSTRAINT PK MyTable PRIMARY KEY NONCLUSTERED  PKGUID   CREATE UNIQUE CLUSTERED INDEX CIX MyTable ON dbo MyTable MyINT    Basically  you just have to explicitly tell the PRIMARY KEY constraint that it s NONCLUSTERED  otherwise it s created as your clustered index  by default  - and then you create a second index that s defined as CLUSTERED  This will work - and it s a valid option if you have an existing system that needs to be  re-engineered  for performance  For a new system  if you start from scratch  and you re not in a replication scenario  then I d always pick ID INT IDENTITY 1 1  as my clustered primary key - much more efficient than anything else

User · Answer

I am currently developing an web application with EF Core and here is the pattern I use  All my classes  tables  have an int PK and FK  I then have an additional column of type Guid  generated by the C  constructor  with a non clustered index on it  All the joins of tables within EF are managed through the int keys while all the access from outside  controllers  are done with the Guids  This solution allows to not show the int keys on URLs but keep the model tidy and fast

User · Answer

Having sequential ID s makes it a LOT easier for a hacker or data miner to compromise your site and data   Keep that in mind when choosing a PK for a website

User · Answer

I ve been using GUIDs as PKs since 2005  In this distributed database world  it is absolutely the best way to merge distributed data  You can fire and forget merge tables without all the worry of ints matching across joined tables  GUIDs joins can be copied without any worry   This is my setup for using GUIDs    PK   GUID  GUIDs are indexed similar to strings  so high row tables  over 50 million records  may need table partitioning or other performance techniques  SQL Server is getting extremely efficient  so performance concerns are less and less applicable  PK Guid is NON-Clustered index  Never cluster index a GUID unless it is NewSequentialID  But even then  a server reboot will cause major breaks in ordering  Add ClusterID Int to every table  This is your CLUSTERED Index    that orders your table  Joining on ClusterIDs  int  is more efficient  but I work with 20-30 million record tables  so joining on GUIDs doesn t visibly affect performance  If you want max performance  use the ClusterID concept as your primary key  amp  join on ClusterID    Here is my Email table     CREATE TABLE  Core   Email         EmailID       UNIQUEIDENTIFIER CONSTRAINT  DF Email EmailID  DEFAULT  newsequentialid    NOT NULL               EmailAddress  NVARCHAR  50     CONSTRAINT  DF Email EmailAddress  DEFAULT      NOT NULL               CreatedDate   DATETIME         CONSTRAINT  DF Email CreatedDate  DEFAULT  getutcdate    NOT NULL             ClusterID  INT NOT NULL IDENTITY      CONSTRAINT  PK Email  PRIMARY KEY NonCLUSTERED   EmailID  ASC     GO  CREATE UNIQUE CLUSTERED INDEX  IX Email ClusterID  ON  Core   Email    ClusterID   GO  CREATE UNIQUE NONCLUSTERED INDEX  IX Email EmailAddress  ON  Core   Email    EmailAddress  Asc

[sql-server] What are the best practices for using a GUID as a primary key, specifically regarding performance?

Examples related to sql-server

Examples related to entity-framework

Examples related to database-design

Examples related to primary-key

Examples related to guid