Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).

The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that. At the same time, I need a way of retrieving the rows quickly in order by [started].

My question is, would it be better to: 1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I 2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).

My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order. Or, is there a better option than the one's I mentioned?

  • I would Remove the uniqueid key and use the date as a clustered index. – eckes Sep 1 '17 at 0:17
  • @eckes dates are notoriously bad for unique keys. Ignoring daylight saving and (negative) leap seconds, there's the problem of clock granularity being sufficient for the rate at which new rows arrive. – Michael Green Dec 15 '19 at 11:21
  • how big the table is and will be ? have you looked into memory optimized tables ? – Kin Shah Apr 16 '20 at 13:10

I would: Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.

Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.

Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.

  • Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places. – eckes Sep 1 '17 at 0:18
  • A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved. – indiri Sep 1 '17 at 4:15
  • Yes but inserts don't move new data, at least not all of it, it enters new pages. – eckes Sep 1 '17 at 9:45

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.