Including PK in the index is unlikely to be of much use. To be used as a matching predicate to facilitate indexed retrieval, your query must contain a sargable predicate against the PK column. If the predicate is equality, eg PK = <value>, then the PK index alone will serve you well. If an inequality, eg PK > <value> or a range, eg. PK BETWEEN <value> AND <value> then the index may be useful, but of course only if the WHERE clause also contains strict equality conditions with the date and time values. In my experience, the latter (strict equality on date/time) is fairly rare, and the former (range comparisons on PK values) is also rare, particularly if the PK values are surrogate values.
I would expect, in the main, for applications to reference rows using PK equality conditions, or ranges of date/time values, neither of which would be helped by this composite index; an index on the date/times alone would be sufficient, in addition to the PK index that is already extant.
Two further points:
answered 28 Apr '11, 08:29
Well, I would think that particular index might make sense if the following sentence (taken from the doc page you refered to) is true:
In your case, it would make sense if there are lots of entries with identical date and time values, and you would frequently select those and have to filter (or order) them further based on the pk. But then the index order would be important, too - i.e. if you sort by date/time chronologically and would use the pk in ascending order, too, the ASC/DESC order in the index columns should be set accordingly.
However, I would expect that an index without the pk column would be nearly as good for that particular situation, as the engine could additionally use the default pk index.
In case you don't use that particular filtering/sorting, I would recommend an index without the pk column (and would usually recommend to use a datetime/timestamp field instead of separate date/time columns).
As always: Some according tests with typical workloads (including looking at the plans) should show the advantages/disadvantages of the different index implementations. You might also use the Index Consultant.
answered 28 Apr '11, 03:50