1

I have a composite primary key, comprising of a datetime, a location id and a 2 character code. This is the key that uniquely identifies each row of my table.

I know that the usage of this table will insert a week of data from a year ago, then a week of data from this year. Therefore, it's likely the table will get fragmented, as the primary key jumps back and forth.

I would like to know what sort of indexing strategies can be employed in this scenario. My gut feeling is that the default clustered index is a bad idea and I should replace that with a non-clustered index, which would make rebuilds easier.

  • 2
    I would have a surrogate key on an identity field and a unique nonclustered index on the composite key columns you mentioned. Also, don't worry about index fragmentation so much. – Erik Darling Jan 12 '17 at 12:40
  • 1
    I wouldn't expect fragmentation to be an issue if source data are ordered, including a sort operator in a INSERT...SELECT from a weekly data staging table. The fragmentation caused by the initial split for each weekly insert should affect query performance. – Dan Guzman Jan 12 '17 at 12:44
1

If you are worried about fragmentation, do you have a maintenance solution on your server that addresses that such as SQL Server Maintenance Solution - Ola Hallengren?


Questions:

  • datetime data type is date, smalldatetime, datetime, datetime2(0-2),datetime2(3-4), datetime(5+) or?
  • LocationId data type is tinyint, smallint, int, bigint or ?
  • Character code data type is char(2), nchar(2) or?

More Questions:

  • How many rows are in your table?
  • Would you need an int or bigint for a surrogate id?
  • What are the other columns in your table?
  • Are all queries filtered by datetime, or are filtered by the locationid and/or character code?
  • Are you always returning the whole row, or sometimes just pieces of it?

Because most people assume the Primary Key is the Clustering Key, I am going to interpret your question as "How to index a composite clustering key with non-sequential inserts?".

The situation you are considering is related to debates like The Clustered Index Debate and Surrogate Key vs. Natural Key.

In this situation, I would want to consider the impact of a [16?] byte composite clustering key vs a 4 or 8 byte clustering key (int / bigint). My decision tree would look something like this:

  1. Will we be using Hekaton (In-Memory OLTP)?

    • Yes => composite key. Run away.

    • No => Good call, continue...

  2. How many rows will this table have?

    • Tens of millions, maybe more! => surrogate key (probably).

      • If the data length of each row will be variable and not narrow => surrogate key.

      • If the data length of the row will be fixed and narrow, and it results in optimal page usage => continue...

    • Less than that => continue...

  3. How will the table be queried?

    • Various combinations of and not always all of datetime, location_id, character_code => surrogate key.

      • In this case you may want to be able to have multiple supporting indexes the combinations of datetime, location_id, character_code for your queries. The clustering key is included in all non-clustered indexes, and the larger it is the more space/pages each index entry will require. => surrogate key.
    • Almost always by all three datetime, location_id, character_code or almost always by datetime or datetime, location_id; but not by location_id without datetime and not character_code without datetime, location_id (zero or only a couple of non-clustered indexes on this table) => continue...

  4. Will any other table reference this table?

    • Yes => surrogate key.

    • No => composite key is a reasonable option


Even if my first run through of the above decision tree leads me to a composite key, I would probably start my design using a surrogate key because it is easier to get rid of it (because it isn't being used) than to go back and add it and implement its use.

Just to clarify, I have had cases where I did find that the composite key was a better solution and did refactor the design to drop the surrogate key. I don't want to leave the impression that the surrogate key is always the better solution, even if it is a common default for many designers (including myself).


Reference:

0

I am not positive on if this would reduce page splits but I would put date last if that has the most changes.

Have a fill factor of less than 100%. Even 90% or 80% will slow down fragmentation significantly.

Have a maintenance plan to reorganize / rebuild the index.

A non-clustered index will use more room but it would make for more efficient maintenance. You can place a unique constraint on a non-clustered index.

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.