Difference in SQL Server performance with one or more primary key columns?

Question

Is there a difference in performance (in terms of inserting / updating and querying) a table if the primary key is one column (e.g. GUID generated for each row) or several columns (e.g. foreign key GUID + offset number)?

I would suggest that query speed should be faster if anything with multiple columns of primary keys, however, would I suggest that the insertion will be slower due to a slightly more complex unique check? I also assume that data like a multi-column primary key can also matter (for example, if one of the columns was a DateTime type, it would complicate it). These are just my thoughts to provoke answers and discussion (hopefully!) And are not based on facts.

I understand that there are some other questions dedicated to this topic, but I am interested and not the problems of management / business.

Answer 1

You will be affected more by (each) component of the key (a) of variable length and (b) width [width instead of narrow columns] than the number of components in the key. If MS doesn't break it again in the latest release (they broke Heaps in 2005). A data type does not slow it down; width and, in particular, variable length (any data type). Note that the fixed len column becomes a variable if it is set to Nullable. Variable len columns in indexes are bad news, because the “decompression” bit must be executed at every access to get data.

Obviously, indexed columns should be as narrow as possible, using only fixed columns, not Nullable columns.

In terms of the number of columns in the composite key, I’m sure that one column is faster than seven, but not so much: three thick columns of variable width are much slower than seven thin fixed columns.

The GUID is, of course, a very thick key; GUID plus everything else is very bold; GUID Nullable is Guiness stuff. Unfortunately, this is a knee-jerk reaction to the solution of the IDENTITY problem, which, in turn, is a consequence of the lack of good natural relational keys. Therefore, it is best for you to fix the real problem at the source and choose good natural keys; Avoid IDENTITY avoid the GUID.

Experience and performance tuning, not a hypothesis.

Answer 2

It depends on your access patterns, read / write relationships and whether, most importantly, a clustered index on the primary key is possible.

The thumb rule makes your main key as small as possible (32-bit int) and allows you to define a clustered index on a monotonically increasing key (I think IDENTITY), if possible, if you don’t have a range search that makes up most of the queries against this table .

If your application is writing intensively and you define a clustered index in the GUID column, you should note:

• All non-clustered indexes will contain a clustered index key and therefore will be larger. This can adversely affect performance if there are many NC indices.

• If you do not use an "ordered" GUID (for example, COMB or using NEWSEQUENTIALID ()), your inserts will fragment the index over time. This means you need regular restructuring of the index and possibly an increase in the amount of free space left on the pages (fill in the factor)

Since there are many factors at work (hardware, access patterns, data size), I suggest you perform some tests and compare your specific circumstances.

Answer 3

It depends on indexing and storage in each case. Other things being equal, the choice of a primary key is not related to performance. The choice of indexes and other storage options will be a deciding factor.

Answer 4

If your situation is aimed at a larger number of inserts, then the smaller the occupied area, the better.

There are two things that need to be separated: the concept of the primary key at the database level and the concept of the key used by your application.

Why do you need a GUID? Are you going to embed in multiple database servers and then combine information into one centralized database?

If so, then my recommendation is the person that the leadership follows. Clustered index on identifier and Unique without GUID clustering. If you use the GUID as a clustered index, then your data inserts will be ubiquitous. This means that your data will not be inserted sequentially, and this will cause performance problems, since your system will insert and move pages randomly.

Having your data added to an ordered fraction, due to identity, is the way to go. You can leave the sort in the index structure (a unique unique version containing a GUID), which is a much more efficient structure for sorting than using table data.