3

Background

I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER.

The reason for considering UNIQUEIDENTIFIER is because there is a good chance we will want a feature that allows records to be moved to other systems (foreign database), and handling all relationships with GUIDs is going to make that so much easier.

Having done some research, there are plenty of debates on the pros and cons of each, the main one I am concerned with is performance. The outcome of my research is that using a UNIQUEIDENTIFIER as the PK will not be a problem as long as it isn't also used as the clustered index for the table.

Ok, so the obvious solution would be to split them into two separate columns:

TableA

ID: INT, Identity, Clustered Index 
PK: UNIQUEIDENTIFIER, Non-clustered Index

Question

My concern here is when I am defining relationships, how will this effect join performance? Take for example, this 'child' table:

TableB

ID: INT, Identity, Clustered Index 
PK: UNIQUEIDENTIFIER, Non-clustered Index
FK: UNIQUEIDENTIFIER, Non-clustered Index

Should I be concerned with any performance loss, particularly with joining tables, by using this approach?

For example, using a query such as:

SELECT * FROM TableB JOIN TableA ON TableA.PK = TableB.FK

Basically, is this the best design for supporting a GUID based PK, or is this going to lead to bad performance? Are there any significant disadvantages to using the approach I suggested?

  • 1
    This is a pretty standard way to do things. My only suggestion would be to make your IDENTITY column a BIGINT -- folks are hitting the int max faster and faster these days (part of the reason why having a GUID for the PK makes a lot of sense). – Erik Darling Jul 26 '17 at 13:38
3

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 methods that you are going to use to get to the data, and the volume of data that you are going to return.

Yes, you will have the fragmentation problem with GUID clustered indexes that will have to be managed, however, if your access method uses that GUID as the query predicate, and you return multiple columns from that table, you may be better off suffering the fragmentation hit in order to improve the performance and lessen the impact of reading that data back out.

Here's a very basic example that show that there is less work required to get the data out when it's the clustered index, and the predicate used to get at the data:

/* NonClustered PK */
CREATE TABLE #T1
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T1 PRIMARY KEY NONCLUSTERED ( C2 )
    );
CREATE CLUSTERED INDEX T1_C1 ON #T1 (C1);

/* Clustered PK */
CREATE TABLE #T2
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED ( C2 )
    );



/* Insert 10 rows into each table */
INSERT INTO #T1
        ( C1, C2, C3, C4 )
VALUES  ( 0, '58BBB460-1AFA-4177-BA78-798DA19E0C97', 'some text', 'C4 text')
, ( 1, '17E8163B-BE21-44C7-A7B7-4997265A139D', 'some text', 'C4 text')
, ( 2, '16AACAB8-CD77-4A8D-BE87-9E433CD157EC', 'some text', 'C4 text')
, ( 3, '787D0714-F92A-4963-89E5-3F5DBF518EA7', 'some text', 'C4 text')
, ( 4, '5C720476-D4BE-4047-9F73-DBB1B6B75208', 'some text', 'C4 text')
, ( 5, 'D70F81C5-8AFF-4ABE-BA64-8F5C1A1C6A90', 'some text', 'C4 text')
, ( 6, '1473E5DC-6F3E-4164-988C-E36EE7C695BE', 'some text', 'C4 text')
, ( 7, '648AEA46-4B45-41F9-AA9B-7129062391B4', 'some text', 'C4 text')
, ( 8, '49497ECB-774D-482D-8230-218E97FB2EB4', 'some text', 'C4 text')
, ( 9, 'B90504FA-CEBA-4383-A61F-82F33DAB7A0E', 'some text', 'C4 text');

INSERT INTO #T2
        ( C1, C2, C3, C4 )
SELECT C1 ,
       C2 ,
       C3 ,
       C4 FROM #T1;

/* Index seek on T1 - no lookup as we include the clustering key */
SELECT C1, C2 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Index seek AND key lookup on T1 because there are columns not contained in the PK */
SELECT C1, C2, C3 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Stil a clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2, C3 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

The things that you need to take into account, and think about are going to be:

  • Storage performance
  • Performance expectations around reads and writes
  • Access methods (columns returned, and columns used for the predicates)
  • Space available (bear in mind the clustering key is included in all nonclustered indexes, meaning that you will use more space, and waste more in the cache

Sadly there is no single correct answer for this, you can only get the appropriate expectations, and performing extensive testing to see if those expectations can be met with whatever data model you design, and if not, look to make adjustments to improve them.

Are there any significant disadvantages to using the approach I suggested?

It is very difficult to say. The only real disadvantage is that, if most of your queries come in via the nonclustered PK, and you are very read heavy you are going to see a great deal of increased IO as you will be performing key lookups on every call.

If your storage can withstand that, then it's great. And on the upside, you don't have the fragmentation problem which can lead to index maintenance hell over time (and can mean that you can't rebuild these things if you are running AGs and don't have maintenance periods).

  • Well, the system can always be designed to query based on the clustered Identity for lookup. Then it would only be joins that require the nonclustered PK and FK. But then FKs are going to be nonclustered regardless of what approach I take, so I guess that isn't a question worth concerning myself with... I think at this stage I am happy to go with my approach thanks to your information. I will revisit the question again over the week and award the accepted answer if nothing better comes along. Thanks again for your time – musefan Jul 26 '17 at 15:13
  • Just remember, that while it may be a pain, you can always revisit that design down the road, and adjust the clustering key if needs be (just don't leave it too long and have a multi-billion row table that you need to change) – Nic Jul 26 '17 at 15:24
  • After some thought, I have decided maybe I am overthinking the PK thing. The PK for the table isnt the thing that needs to be globally unique. In order to handle database merging, I only need the columns that are used for relationships to be globally unique. Sure I will get the downside of joins happening on GUIDS rather than INTS, but that would have been the case either way. Just thought would run it past you in case you spot any obvious problem I might have overlooked? – musefan Jul 27 '17 at 10:41
  • Nothing glaring I can think of. – Nic Jul 27 '17 at 13:38
2

Your choice of clustering key is one thing; your choice of primary key is something else. Don't automatically cluster on the PK just because it's the default behavior in SQL Server.

  • The clustering key should be as narrow as possible, because all other indices will point to it. An index on an INT in a table with an INT CIX will be eight bytes per record; the same for a table with an UNIQUEIDENTIFIER CIX will be 20 bytes per record.
  • To keep updates cheap, it should be durable. A change to the clustering index must be propagated to all other indices.
  • To minimize fragmentation, it should be always-increasing (e.g., with NEWSEQUENTIALID()). I've seen conflicting reports as to how important this is; Google "hot spots" and "GUID primary keys" for a lot of opinions. With SSDs, serial data is not as important as it once was, but it's still expensive to do extra page lookups because your data is fragmented.
  • Ideally, it should be unique. If it's not, SQL will add a 32-bit uniquifier, so if you have a unique INT you might as well include it in the clustering index rather than let it be non-unique.
  • Scans against the clustering key can retrieve any and all fields without making a separate hop. If you expect to do a lot of range scans, cluster on that field. E.g., you'd cluster Orders by DateOrdered to support queries of sales by month for internal analysis and reporting, or cluster it by CustomerID to support queries of all orders for a customer for a customer portal or support desk.
  • The primary key should also be as narrow as possible, because other tables' foreign keys will generally join to it. Saving space is a good practice in general.
  • Primary keys exist for the DBA's convenience as much as anything: to ensure that you can uniquely identify records; as a target for foreign keys

Without knowing more about your data, it's hard to make a specific recommendation. At 16 bytes, a UNIQUEIDENTIFIER is certainly wider than an IDENTITY INT, but the cost (not just storage, but brain space) of maintaining a second key is real too. Unless you have hundreds of millions of records in the table, I'd start with the simplest solution: a clustered PK on a single UNIQUEIDENTIFIER field, populated with NEWSEQUENTIALID().

  • The big problem with NEWSEQUENTIALID() for me is what happens when you change servers (let's say failover a FCI or AG). Or have to cut to a DR server. The other problem comes with global uniqueness and the potential for keys to move between global constructs (although admittedly far less of an issue). – Nic Jul 27 '17 at 13:40
  • My understanding is that a GUID, including one generated by NEWSEQUENTIALID(), includes the MAC address of the generating machine (or a hash thereof), (virtually) ensuring uniqueness across machines, while the timestamp part ensures uniqueness within a machine. It's true that data from different machines will have different hotspots, but at least you're not writing data equally throughout the tree like you would with a random key. – Jon of All Trades Jul 27 '17 at 15:39
-3

Your research is wrong. Index on GUID will fragment if it clustered or non-clustered. It just takes a little longer to defragment a clustered index.

If you have a PK GUID then what purpose does an int ID serve?

  • 1
    Is this an answer, or a comment? The purpose of the int ID would be to prevent the clustered index from fragmenting. The system can cope with querying on the ID. The only purpose for a GUID PK at all is to cope with foreign database merging, specifically to ensure relationships are maintained correctly. Personally I would much rather work with just an INT ID, but I cannot do that so I must try to find the best way to work with GUID PK – musefan Jul 26 '17 at 15:41
  • Your example query uses the GUID. The system cannot cope with query on ID if data is moved to another system. Non-clustered index does not prevent fragmentation. – paparazzo Jul 26 '17 at 15:47
  • The system can cope because queries are not hardcoded, they use the ID for the record that they want to load, which is obtained dynamically based on the records in the database. It would be a pretty poor application if it needed to remember what ID's where usable... – musefan Jul 26 '17 at 15:59
  • 1
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – mustaccio Jul 26 '17 at 17:04
  • 1
    @mustaccio GUID will still fragment on a non clustered is an answer to the question. OP is concerned about performance and assumes GUID on nonclustered index has no performance impact. – paparazzo Jul 26 '17 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.