November 20, 2014 at 8:20 pm
Comments posted to this topic are about the item Index Behaviour
Thanks,
Shiva N
Database Consultant
November 20, 2014 at 8:21 pm
Good, Interesting question.
Thanks.
November 20, 2014 at 11:49 pm
Would the first query not use the clustered index?
It makes no sense why it would even consider the non-clustered index
November 21, 2014 at 12:43 am
interesting question.
Can you explain why the other answers are wrong?
November 21, 2014 at 1:23 am
Nice question, not so great explanation.
Still wonder why number 1 is not a clustered index scan.
Does SQL Server always prefer a covering nonclustered index over a clustered index?
(still got it right though, by process of elimination)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 21, 2014 at 1:45 am
This was removed by the editor as SPAM
November 21, 2014 at 2:28 am
It was possible to work this out by elimination - either both or neither of 4 and 5 had to be there, which ruled out all but 1 and 1,3. Perhaps coincidentally, all the other options would fail with errors on my case sensitive database.
If 1 was there then 3 had to be as well. If there'd been an answer of "none of them" then I may well have got it wrong.
November 21, 2014 at 3:10 am
I have to say I thought all the queries would use the non-clustered scan as it was just as quick to do a scan than a seek.
November 21, 2014 at 4:06 am
was not sure about the outcome, tried it out :
all five of them used the nonclustered index (SQl Server 2012).
This is a little bit of a surprise, but also enlightening:
both indexes in fact cover the same date (the nonclustered index must include the cluster key),
so in some situations the nonclustered index is better than the clustered index,
but worse in none of the five cases.
This means the situation is extremely unstable and the outcome may change every day.
November 21, 2014 at 5:27 am
Koen Verbeeck (11/21/2014)
Nice question, not so great explanation.
+1. Good question, but the explanation could have been a whole lot better.
November 21, 2014 at 6:41 am
As a SQL newbie, I use the QotDs to help me learn. I may not get most of the answers right, but at least I learn something from the explanation. This was not one of those times.
With this question, the explanation didn't tell me anything -- it amounted to "this is right because it is." The reference linked didn't explain either -- that page was an overview of indexes in general.
Could someone please explain why the answer is the right one?
(For the record, my answer prior to looking at the choices was "1,3,4,5", so clearly I don't know a lot about how indexes work.)
November 21, 2014 at 7:16 am
gordon.feeney (11/21/2014)
I have to say I thought all the queries would use the non-clustered scan as it was just as quick to do a scan than a seek.
That was my first thought too but figured it must still perform a seek even on this tiny table or the question would be pointless. If there had been a "none of them" option that would have been my choice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 7:19 am
Great question but the explanation and the linked article were terribly disappointing. The article says to use the table designer for creating indexes after it explains what a table is in sql server. Even worse, that article doesn't even pretend to go into the differences between index scans and seeks.
If the explanation had any substance and the linked article was relevant to the question this would have been spectacular.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 7:42 am
I also thought select #1 would use a clustered index scan. Interesting that it doesn't. I have tested this out on a DBA table that captures CPU utilization for one of our instances. The table has 367,000 rows in it. This table has a clustered index on an ID int Identity(1,1) field, and a nonclustered index on a CaptureDateTime datetime field and includes the other three non-PK fields on the table. Running:
SELECT *
FROM dbo.CPUUtilization
does a nonclustered index scan. So I guess whenever you have nonclustered index that covers all the fields, the optimizer will prefer the nonclustered index.
Good question!
Be still, and know that I am God - Psalm 46:10
November 21, 2014 at 7:49 am
A rule of thumb, David, is that the query optimiser will go for the smallest structure available so it can read more of it into memory- i.e. you can read more pages of a small index that 'covers' the query into memory than you can a larger (clustered for example) index. However in this case the clustered and non-clustered indexes are the same size so I too was puzzled as to why it would choose one over the other.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply