11

Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (Meaning of Primary Key to Microsoft SQL Server 2008) has shown that it is not necessary to set PK and clustered index to be equal.

So how should we choose clustered indexes then? Let's have the following example:

create table Customers (ID int, ...)
create table Orders (ID int, CustomerID int)

We would usually create the PK/CI on both ID columns but I thought about creating it for Orders in CustomerID. Is that the best choice?

Dale K
  • 25,246
  • 15
  • 42
  • 71
usr
  • 168,620
  • 35
  • 240
  • 369
  • Possible duplicate of [SQL Server - When to use Clustered vs non-Clustered Index?](https://stackoverflow.com/questions/18304376/sql-server-when-to-use-clustered-vs-non-clustered-index) – Michael Freidgeim Feb 24 '18 at 00:55

3 Answers3

13

According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily:

  • Unique
  • Narrow
  • Static

And if you can also guarantee:

  • Ever-increasing pattern

then you're pretty close to having your ideal clustering key!

Check out her entire blog post here, and another really interesting one about clustering key impacts on table operations here: The Clustered Index Debate Continues.

Anything like an INT (esp. an INT IDENTITY) or possibly an INT and a DATETIME are ideal candiates. For other reasons, GUID's aren't good candidates at all - so you might have a GUID as your PK, but don't cluster your table on it - it'll be fragmented beyond recognition and performance will suffer.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Are these blog posts still just as relevant to newer versions of SQL Server, or have recent performance tweaks in SQL Server 2008 and later changed the best practices somehow? – Adrian Grigore Sep 17 '14 at 16:21
  • @AdrianGrigore: everything's still valid, as long as you use "normal" tables (e.g. not datawarehouse / columnstore stuff) – marc_s Sep 17 '14 at 16:24
  • @marc_s Thanks for the guide , I read the Kimberly's article too and i'm confused , I hope you help me , She said it's OK to use guid with default sequentialId() , while you say they aren't good candidates at all , I'm wondering not to have Clustered index on my table or have clustered index on sequential guid , – Mostafa Aug 07 '15 at 08:24
  • 1
    @Mostafa: Guids are always bad since they're too big (twice as big as bigint, four times bigger than int). With `newsequentialid()`, at least they're not totally random - it's *better* than with `newid()`, but it's still worse than `int` or `bigint`. If you **must** use guid for whatever reason, then **make sure** to use `newsequentialid()` as the default clause for that column. But if you ever can - avoid guid as clustering key altogether – marc_s Aug 07 '15 at 08:26
  • I would say biggest problem is fragmentation even more than the size. – Ernesto Aug 07 '17 at 17:46
6

A best candidate for a CLUSTERED index is the key you use to refer to your records most often.

Usually, this is a PRIMARY KEY, since it's what used in searches and/or FOREIGN KEY relationships.

In your case, Orders.ID will most probably participate in the searches and references, so it is the best candidate for being a clustering expression.

If you create the CLUSTERED index on Orders.CustomerID, the following things will happen:

  1. CustomerID is not unique. To ensure uniqueness, a special hidden 32-bit column known as uniquifier will be added to each record.

  2. Records in the table will be stored according to this pair of columns (CustomerID, uniquifier).

  3. A secondary index on Order.ID will be created, with (CustomerID, uniquifier) as the record pointers.

  4. Queries like this:

    SELECT  *
FROM    Orders
WHERE   ID = 1234567

    will have to do an external operation, a Clustered Seek, since not all columns are stored in the index on ID. To retrieve all columns, the record should first be located in the clustered table.

This additional operation requires IndexDepth as many page reads as a simple Clustered Seek, the IndexDepth beign O(log(n)) of total number of the records in your table.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
1

If you're concerned about clustering it's usually to help improve data retrieval. In you example, you're probably going to want all records for a given customer at once. Clustering on customerID will keep those rows on the same physical page rather than scattered throughout multiple pages in your file.

ROT: Cluster on what you want to show a collection of. Line items in a purchase order is the classic example.

No Refunds No Returns
  • 8,092
  • 4
  • 32
  • 43
  • Line Items on an PO might be good idea for a cluster, but not if there's only 2 or 3 (or a dozen) line items on the typical order. Unless the rows you are clustering together starts to get into the dozens or hundreds, then it's better just to let SQL Server perform the bookmark lookup. i had a system where business requirement had to find all the "line items" that happened during a particular cashier's shift (to see if they balanced). Denormalizing the "line items" with the `id` if the **Shift**, and then clustering on **Shift** was a huge speed boost. – Ian Boyd Apr 22 '10 at 11:40