February 26, 2010 at 8:59 am
Hello,
I have a problem. When use index covering and when clustered index? I examine the cost of different queries such as number of physical and logical reads, query execution time, using tables of various size it means from 10000 to 2000000 rows. query also returns differ number of rows. To be honest i can not find any principle/rules/regularities. The results are similar to each other, of course not the same but i expected larger differences between these two 'techniques' of indexation.
I also wonder whether the use of the clustered index is not some kind of use of index covering what would explain the similar results. Clustered index contains data within the leaf so in both techniques there is no need to go to table.
Let's say that i have a table
TAB(id,col1,col2,col3)
Query : select col1,col2,col3 from tab where id=4
We are creating index create clustered index cl_ind on TAB ( id)
Data will be take directly from cl_ind index excepting table
Let's forget/delete that we created clustered index and let's create non clustered covering index
create nonclustered index noncl_ind on TAB ( id, col1,col2,col3)
The same situation data will be take directly from noncl_ind index excepting table so very similar technique so maybe thats way i am getting similar results??
What do you think? are there any expert with experience in this area? When to and when not to use covering index/ clustered index? (in which situations for which special queries etc) and why i am getting similar result?
the second additionally question about proper column order in index definition. When i am creating composite index which column should be first? most selective from WHERE clause? then less and at the end the least?? am i right?? or in the order exactly as they are in WHERE clause? And what about columns from mentioned in SELECT ?? do their order is important??
February 26, 2010 at 10:11 am
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2010 at 6:47 am
@GilaMonster . Thanks a lot for links. I've read a lots of articles but not these mentioned by you. Generally very good and usefull. But..... 🙂
These articles answer perfectly for my 'second additionally question' but i still have doubts regarding first part of my post above. It means why i am getting similar results examining queries using index covering and clustered. If it is because clustering index "is some kind of index covering?/ is similar to index covering"??????? In both 'techniques' we are reading data directly from index so maybe that's way my examine's results are similar???? what do you think???? Similar results i have only between index covering and clustered index. I see big differences in costs between rest of techniques it means between clustered/covering index and 'full scan' or single non clustered index. That's way i am asking only about covering and clustered index.
Regards,
Fabi
February 27, 2010 at 8:22 am
A clustered index contains every single column in the table in it's leaf level. Hence it is covering for any query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply