Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Skywaker
Starting Member
3 Posts |
 Posted - 2008-05-19 : 13:55:45
|
| hello, this is my first post.i'll describe the problem with an example.i've a table with many columns, let's say that:colum2colum3colum4colum5colum8 are PK/Clustered Index.the problem that i'm seeing is that when my query filters only with colum5 and colum8 (... where colum5 = X and colum8 = Y...) for example, it doesn't use the clustered index, it uses a non-clustered index. But when i query using colum2 and colum3 (... where colum2 = X and colum3 = Y...) it does use the clustered index. I don't understad why it doesn't use the clustered index in the first case. I'm querying using two colums contained in a clustered index. Is it important the order of the columns in a clustered index? 'cos colum2 and colum3 are the two first ones of that index.any help/words about this?thanks in advance!!Edward.- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-19 : 13:58:13
|
| The order of the columns in the indexes is very important. If you are going to query on columns further down in the index, then I'd suggest adding non-clustered indexes to cover those. You could force the clustered index to be used in the execution plan via an index hint, but you may see worse performance. The query optimizer typically picks the best execution plan as long as you've got statistics and they've been updated recently.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-19 : 14:08:27
|
The clustered index seems very wide.Do you know which non-clustered index the query optimizer chooses?The query optimizer calculates the amount of IO needed to reach the final resultset and using a non-clustered index may be lighter to use.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Skywaker
Starting Member
3 Posts |
Posted - 2008-05-19 : 14:13:18
|
| but, so , is it useless to have a clustered index with more than one colum? if it is not using the "last" columns of the clusterd index, and prefers a non-clustered instead, what's the point of having a clustered index containing the columns that my queries are using? thanks for your replies!!!Edward.- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-19 : 14:14:57
|
| 1. It is not useless to have a clustered index with more than one column.2. It depends on your queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
Skywaker
Starting Member
3 Posts |
Posted - 2008-05-19 : 15:16:08
|
| well thanks, it's weird, i've been reading a sql server book called "Running SQL SERVER 2000" from McGraw-Hill, it explains b-trees. So i thought that access time filtering with columns from the clustered index where all independent. At root level i saw all the columns contained in the index. But this beheavior i'm asking about is more like a binary tree, not a b-tree. Maybe i'm confused but if i have a b-tree i could choose any column contained in the clustered index and the result should be the same, but this is not what happens, the result is good only if you choose the first columns in the clustered index.thanks for your time!! :-) |
|
|
|
|
|
|
|
|
query not using clustered index