Composite Indexes

[scotward]

I have read that "every table should have at least one unique clustered index". I have also been told that if a column is a member of a comopsite index, queries that group or have 'where' clause on that column will not perform as well as if that column were indexed independently. So here is my dilema:

The unique clustered index consists of three columns. One of these columns is frequently used in groupings, where's and table joins. Should I create a new unique key so this field can be indexed independently?

 

[SQLSister]

Personally, this is one of those things, I would test both ways. Try it with the extra index and without. Look at the execution plans of the queries which use the indexes and see which is faster.

 

[JamesLean]

It depends on the order of the columns within the index.

Say you have 3 columns in your table: c1, c2, c3. You create your clustered index:

CREATE CLUSTERED INDEX myindex ON t1(c1, c2, c3)

If your query searches on c1 (

WHERE c1 = 123

) then it can utilise the index properly.

However, if you search on c2 or c3 then it cannot properly use the index. In this case it may be useful to create nonclustered indexes on c2 or c3, depending on which you're using.

--James