Clustered index in SQL Server 2008

I have a table with a composite primary key. It creates clustered index. If I use a few columns from that composite primary key in a WHERE clause, will that index be still effective? Or do I have to make new index based on the columns used in开发者_运维百科 WHERE? Any help would be appreciated.

Any index, whether clustered or not, is only useful to a query provided it's Left-most columns in the definition are all part of the WHERE clause.

If you have an index on (Col1,Col2,Col3), then this index can be useful for WHERE clauses that use all 3 columns, or Col2 and Col1, or just Col1. But as soon as Col1 isn't included in the search, the index is useless.

If ever possible, I try to avoid composite keys - especially the primary key. Also: if you have a composite key, it's only effective if you use the left-most n columns, e.g. if you have a column at the third position in your composite key and your search only has a WHERE on that third column, the index won't be able to be used.

The clustering key would ideally be a small, stable, unique and ever-increasing column - INT or BIGINT as your default option. Don't overload your clustering key! Don't make it too wide, and by all means, try to avoid columns of varying size (like VARCHAR - they carry additional overhead)

There's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!