Question on Indexes

[byrne1]

What are the ramifications of having four fields included in a single clustered index? My table has 3.5MM records in it and there are four fields that I search on regularly. Since SQL 7.0 only allows one index to be clustered, I have put all four fields into one clustered index. Should I put them into four seperate indexes instead?

 

[tlbroadbent]

SQL Server allows one clustered index because the data is physically ordered in sequence of the clustered index.

If you search on all four columns at the same time, then create one index. However, if you search on the individual columns, you should create indexes on each column.

A clustered index may or may not give you better performance that a non-clustered index. There are lots of good articles about indexing. However, I find that experimentation is always the best way to determine which indexes will work best.

http://www.sql-server-performance.com/optimizing_indexes.asp

http://www.sql-server-performance.com/clustered_indexes.asp

http://www.sql-server-performance.com/nonclustered_indexes.asp

http://www.sql-server-performance.com/indexes_not_equal.asp

http://www.sql-server-performance.com/nb_index_wizard.asp

http://www.sql-server-performance.com/index_tuning_wizard_tips.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

If you use queries that search just on the different individual fields within the table then yes you should have separate indexes. If you have all four fields as the clustered index then the 2nd , 3rd and 4th fields would be not be used as an index if you were only searching on that part of it. The query would scan down the whole table.

Still keep a clustered index, as this will ensure all rows in the table are sorted in the clustered index key order. A clustered index is useful for range retrieval queries. For example Surname in a customer table would be a good choice for being clustered.

Also bear in mind that your primary key is also an index.

Rick.

 

[byrne1]

Thank you!