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 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.
November 21, 2014 at 8:22 am
Koen Verbeeck (11/21/2014)
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)
Agreed on the explanation.
The NCI is chosen in this case because it is a smaller index and the record count has a bit to contribute as well. A CI could have been chosen had the number of columns been greater, the number of records been greater.
Take the following rewrite with a completely fabricated table but still more realistic than the table in the question.
Use Sandbox2
go
if object_id(N'Emp') is not null
begin
drop table Emp
end
go
--create table Emp(Empid int identity(1,1),Fname char(100),Lname varchar(100))
DECLARE @BeginDate DATE = '2014-10-01'
,@EndDate DATE = '2014-10-31'
SELECT TOP 1000000
EmpID = IDENTITY(INT,1,1),
Blinky= 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL('', 0)),
CandyMan= ABS(CHECKSUM(NEWID()))%50000+1,
FName= CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
LName= CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeDate= DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate),
BadData= RIGHT(NEWID(),12),
Amount= CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY)
INTO dbo.Emp
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 ;
create clustered index pk_Emp_Empid on Emp(Empid)
create nonclustered index IXNC_Emp_Fname_Lname on Emp(FName,LName)
go
In this case, we will see that the first and third queries resort to a CI scan instead of the index scan. More data, different data, more columns all contribute to the selection of the CI instead of the NCI. For a query with better data distribution the QO thinks it is less costly to do a CI scan so it will use that. In the original question, the cost of the NCI scan won out because the table had a single record and very few columns so the cost was tiny.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply