Wilfred van Dijk (10/3/2008)
Generally, you'll get the most benefit of a (clustered) index if you add columns which are most used in your where clause.In your case EMPLID. But Clustered indexes have to be unique, which is not in your case.
But, if you create a clustered index on a non-unique field, SQL will add a column to make this index unique.
So you have to compare the extra diskspace against the performance benefits.
You could also consider partitioning. A history table is a perfect candidate for that feature.
Not necessarily - your clustered index should always* be monotinic. If as an extreme example, you're writing a Db for a web facing app using GUIDS even though you're going to be using the GUIDS for lookups and queries you DO NOT use them for your clustered index as the random nature of the GUID means that the new records can end up going anywhere in your table. This can lead to huge performance issues, page splits, pagelocks as everything is reordered in the table to place the row in the right physical position. You'd probably use insert datetime and cover the lookup using a covering index
* Well, no rules hold fast all the time. Sure someone'll think of a situation where this wouldn't be the case I'm sure