6

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.

I've got a product history table that is inserted into frequently (never updated) and the table has this kind of structure

  1. ProductNo String(20)
  2. CreatedDateTime DateTime
  3. Description String(100)

At the moment the primary key is made up of a combination of ProductNo and CreatedDateTime in an attempt to define a unique index key. We can have many records per productno.

I'll be creating some 1 to 1 related tables and don't want to carry both the productno and the createddatetime fields into the related tables to act as foriegn keys. I also think this combination is a little fragile in order to guarantee uniqueness.

So, I'm planning to add a new field to the table 'ProductHistoryPK' as an incrementing Int or SequentialGuid to act as the primary key and a foreign key to related tables.

In terms of indexes I'm thinking of creating

  1. Non-clustered primary key on the new ProductHistoryPK field.
  2. Clustered Index on the ProductNo field as this is field that is often searched on.

Any thoughts or pointers regarding this?

Thanks...

1
  • The fact that ProductNo is searched on often doesn't really qualify it as the clustering key, in my opinion. See my answer for what you should consider when picking your clustering key - a varchar(20) column isn't really well suited....
    – marc_s
    Feb 22, 2012 at 12:16

3 Answers 3

9

You are correct to separate "clustered index" from "primary key":

  • A clustered index is the organisation of data on disk is better if
    • narrow
    • numeric
    • increasing (strictly monotonic)
  • The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

  • ProductHistoryID
  • ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

  • clustered index should be on ProductHistoryID
  • unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

or

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"

5
  • Thanks for reply, if we use the ProductNo as the main search key on this table, wouldn't I be better off creating a clustered index on this field in order to group the records together for a certain product????
    – Bendy
    Feb 22, 2012 at 11:01
  • 1
    @Bendy: A wide clustered index will increase IO, memory usage and fragmentation. And decrease performance. A non-unique clustered index widens it further with a "uniqifier". Your choice...
    – gbn
    Feb 22, 2012 at 12:29
  • thanks for this, I'm leaning back towards your solution after reading some of the really valuable information in these posts, many thanks..
    – Bendy
    Feb 22, 2012 at 13:14
  • Something I've often wondered: if all queries filter on CreatedDateTime, would the compound (CreatedDateTime, ProductHistoryID) (in that order) make a better clustered index?
    – onedaywhen
    Feb 24, 2012 at 11:32
  • @onedaywhen: don't know. It's still wide. I'd also consider having an indexed view for "current items" anyway as per type 2 SCD
    – gbn
    Feb 24, 2012 at 11:54
5

I just want to stress one thing: please very carefully pick your clustered index!

It's the most replicated data structure in your SQL Server database (assuming it's SQL Server you're talking about). The clustering key will be part of each and every non-clustered index on your table, too - certainly in the leaf level, possibly also in the index navigation structure.

You should use extreme care when picking a clustering key - it should be:

  • narrow (4 bytes ideal)

  • unique (it's the "row pointer" after all - if you don't make it unique, SQL Server will - for you - in the background - costs your a couple of bytes for each entry - times the number of rows and the number of nonclustered indices you have - can be very costly!)

  • static (never change - if possible)

  • ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)

  • it should be non-nullable and ideally also fixed with - a varchar(250) makes a very poor clustering key

Anything else should really be second and third level of importance behind these points ....

See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!

1
  • 1
    Thanks for this Marc, I've always typically used an int based primary key, clustered and created non-clustered indexes on key used for searching / joins. I think I'll create a int based producthistoryid field, create a clustered primary key and create non-clustered indexes on the productno field. I was concerned that index fragmentation could be a issue so having an incrementing key makes sense, thanks
    – Bendy
    Feb 22, 2012 at 13:10
2

This is an interesting question. In practice, yes, we prefer that the primary key is a simple, unique, numerical field, but, often, this has less meaning to well established business keys. So, you may be doing yourself a dis-service by forcing a new key onto them.

Unless there are plans to do a major data redesign, I'd personally recommend continuing what works, provided that it does work.

You can make a primary key on the two columns as follows:

CREATE TABLE HISTORY
(
    ProductNo NVARCHAR(20) NOT NULL,
    CreatedDateTime DateTime NOT NULL,
    Description NVARCHAR(100),
    CONSTRAINT PK_HISTORY PRIMARY KEY (ProductNo, CreatedDateTime)
);

You can also make a foreign key constraint that enforces the two columns to be in use.

8
  • Thanks I'm just concerned about how unique this combination of keys really is...
    – Bendy
    Feb 22, 2012 at 10:59
  • A primary key can be defined over two columns.
    – BicycleDude
    Feb 22, 2012 at 11:01
  • Thanks BicycleDude, I know that , just that the individual values could be the same adding up to the same combined key value, if user one creates a record for productno 'ABC' and '25/01/2011' and user 2 does the same then we'd have duplicate key values...Thanks for the input
    – Bendy
    Feb 22, 2012 at 11:07
  • Okay @Bendy, I think you mislead us when you said ProductNo and CreatedDateTime is a primary key which implies uniqueness. What you're saying they aren't really unique, and therefore, they cannot be really primary keys.
    – BicycleDude
    Feb 22, 2012 at 11:10
  • 1
    Also @Bendy. I feel the numerical field is more likely going to be a better bet. But, this goes with the same caveat that you'll need to prepare your business to accept a data model change. As far as sequencing goes, you should consider using IDENTITY keyword and let SQL Server auto number your fields.
    – BicycleDude
    Feb 22, 2012 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.