SQL Primary Keys

Question

So, a co-worker and I are in an argument on which way is better for generating primary keys that are GUIDs.

We are using .NET 4.0 with Entities 4 and using stored procs to make select/insert/updates.

He wants to create GUID primary key in code and pass it back as part of the insert using the Guid class or/and using some created Sequential GUID class.

I want the GUID to be created by SQL Server on insert using either newid() or newsequentialid().

My argument against his way is that if you have to do multiple inserts you have to make a roundtrip to get a guid for each insert so you maintain that relationship for your foreign key constraints. Plus, using this way you have to make several roundtrips for each insert.

His argument about using SQL to do is that he doesn't have access to the key BEFORE the insert happens and has to wait for the insert to happen to get the primary key guid back to use in other parts of code. This way you can make one connection to a stored proc and it handles all the inserts.

So, Which method is better for single inserts? Which method is better for multiple inserts in a transaction?

Answer 1

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:

1) 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.

2) 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. If you insist on GUID, then at least use the newsequentialid() method on the server!

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!

Marc

Answer 2

When I have questions like these I say to myself "SQL Server is good at sets, so lets let it do what its good at" and sometimes "1 is just a specific case of N".

Which method is better for single inserts?

The single insert time will be the same for either of your approaches for a synchronous sql call. However "his" approach will give you more problems with seek time down the line because his sequential guid method won't be as good as sql servers (and you will probably lose the global uniqueness). It will also split your code base when you inevitably need to do multiple inserts.

Which method is better for multiple inserts in a transaction?

If you are arguing a set based insert ( insert / select ) v.s. a single line insert (insert into), the set based is going to win on multiple inserts because the trip back to the client is going the expensive part.

If this were me I would create a SP that takes a serialized collection of the objects to insert, does an insert / select with an output clause, check out "Example B. Using OUTPUT with identity and computed columns" on this page, let sql server create the GUID (if you are stuck on it) and return to the client or run the next statement in the SP to insert child rows based on the output table your insert generated.