I have tables where the primary key is something like this:
-StoreID
-BusinessDate
Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a daily basis and is never updated and almost never deleted.
I think is would be better if the field order would be:
-BusinessDate
-StoreID
My reasoning is that the sort order of this key would more naturally follow the typical insert order. I'm wondering if I should change the order of the fields in the key.
So far performance has been acceptable. Will the current field ordering cause serious performance problems in the future if I don't change anything?
Best Answer
The order of columns in a primary key does not affect the insert performance, since the combination of the values in the two columns of the primary key are meant to be unique.
However the order of index impacts the performance of SELECT queries. In the new order you will have a greater and increasing range of values (dates) across which the index is clustered (dates) as compared to clustering around 100 stores.
If most of your queries are focused on a single store with the new ordering your queries will be slower.