Index Behaviour

Question

Index Behaviour

November 24, 2014 at 6:32 am

This was removed by the editor as SPAM

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

TomThomson (11/22/2014)
h.tobisch (11/21/2014)
Just understood:
a nonclustered index may be the same size but never larger than a clustered index, so, if it covers the data the nonclustered index will be first choice.
I don't believe that that is correct. The non-clustered index can be larger (require more pages of storage) than the clustered index.
In a case like the index here, the leaf level of the non-clustered index is the same size as the leaf level of the non-clustered index. If there are enough rows the non-leaf levels of the non-clustered index will be bigger than the non-leaf levels of the clustered index simply because the cluster key is smaller than the other index's key, so that the total size of the non-clustered index will be greater than the total size of the clustered index.

+ 1, Yes true, I have some cases where my non-clusetered indexes > the clustered one.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

h.tobisch SSCommitted Points: 1671 More actions · Answer 2

Will a total scan of an index use anything but the lef level ?

karthik babu Hall of Fame Points: 3335 More actions · Answer 3

I still wonder, the results of 2 and 3. One is scan and the other is seek on the same non-clustered index. Is there any explanation for this SQL behaviour?

[font="Courier New"]-- 2.

select *

from Emp

where fname='fname'

-- 3.

select *

from Emp

where Lname='Lname'[/font]

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

November 24, 2014 at 9:16 am

#1760563

Thanks for the question.

karthik babu Hall of Fame Points: 3335 More actions · Answer 5

Stewart "Arturius" Campbell (11/24/2014)
karthik babu (11/24/2014)
I still wonder, the results of 2 and 3. One is scan and the other is seek on the same non-clustered index. Is there any explanation for this SQL behaviour?
[font="Courier New"]-- 2.
select *
from Emp
where fname='fname'
-- 3.
select *
from Emp
where Lname='Lname'[/font]
The order in which columns are declared in the index is important.
Given that the index is created with fname then lname, the engine expects the WHERE clause to contain those columns.
As to why the query 2 uses a clustered index seek and query 3 a scan, think of it as follows:
select * from NonClusteredIndex WHERE criteria like 'fname%';
select * from NonClusteredIndex WHERE criteria like '%lname';

Nice explanation!! Thanks Stewart

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 6

DrKiller (11/20/2014)
Would the first query not use the clustered index?
It makes no sense why it would even consider the non-clustered index

Good question, though the explanation is a bit lacking and the link is better removed - that article appears to be written by someone who understands just enough to be dangerous.

The nonclustered index can in this can be used to satisfy the query, because all required columns are included (fname and lname are indexed; Empid is included as the clustered index reference). Since nonclustered indexes use slightly less per-row overhead bytes in their leaf pages, they are sometimes one or two pages smaller, even when all columns are included. That makes them cheaper to scan than a clustered index; for that reason the optimizer will always prefer a nonclustered index over a clustered index.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Hugo Kornelis (11/26/2014)
DrKiller (11/20/2014)
Would the first query not use the clustered index?
It makes no sense why it would even consider the non-clustered index
Good question, though the explanation is a bit lacking and the link is better removed - that article appears to be written by someone who understands just enough to be dangerous.
The nonclustered index can in this can be used to satisfy the query, because all required columns are included (fname and lname are indexed; Empid is included as the clustered index reference). Since nonclustered indexes use slightly less per-row overhead bytes in their leaf pages, they are sometimes one or two pages smaller, even when all columns are included. That makes them cheaper to scan than a clustered index; for that reason the optimizer will always prefer a nonclustered index over a clustered index.

Good to know, thanks for the explanation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ericksrm SSC Enthusiast Points: 167 More actions · Answer 8

Process of elimination worked for me too... but slightly different logic.

I thought 3 was the only one that would definitively result in a NC scan. This left answers (1,3) and (1,3,5). Query 5 is covered by the index, so this would seek (not scan). Therefore the only possible answer for me was (1,3).

I would have gotten this one wrong if the author provided (3) as an answer... thinking along with others that query 1 would have resulted in a Clustered Scan.

Feivel SSCertifiable Points: 7524 More actions · Answer 9

It doesn't make much of a difference with this set of data, as even you force QO to use a clustered index the cost is exactly the same. I've tested this, and the explanation isn't worth the effort but the question definitely keeps you thinking twice...:-D