Non Clustered Index on a range

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Non Clustered Index on a range

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-09-14 : 00:46:19

Quick question about Non Clustered Index that is being used on a range:

Assuming there is a clustered index on the table already and the non clustered index column is the one used on the query ... how many times are needed to find all the pages?

For example

--col1 has a non clustered index
--col2 is the clustered index
select col2
from table
where col1 between 1 and 1000

Would it be correct to say that the non clustered Index B-tree will have to be traversed one time only and then goes to the clustered B-tree, ending to leaf level that is already in order? <-- one trip here

Or

"X" times ? which is one "trip" per every page that is needed to satisfy the range?

Which one is correct?

I know this is a very simplistic scenario and will depend of many factors, but wanted to know, in general terms, how many times are needed to return each and all the values.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-14 : 01:42:36

The clustered index would not be used in this situation as the NC index has all of the data required to satisfy the query.

-Chad

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-09-14 : 07:40:50

quote:
Originally posted by sql-lover

[quote][originally posted by chadmat[/i]

The clustered index would not be used in this situation as the NC index has all of the data required to satisfy the query.

-Chad

The Clustered Index Index is always used by a non clustered Index.

And if the query above is covered, my bad, so let me change:

--col1 has a non clustered index
--col2 is the clustered index
select col1
from table
where col1 between 1 and 1000

So I guess that my question is still open?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-14 : 12:11:25

Again, the NC index has all of the data to satisy the query, so the Clustered Index would not be used (I'm not sure why you think the Clustered Index is always used...it is not).

--col1 has a non clustered index
--col2 is the clustered index
select col1, col3
from table
where col1 between 1 and 1000

This would require a Bookmark Lookup for each row returned, and it depends how many rows are in the table, if the number of rows returned by this query is more than about 5% of the table, it will forgo using the NC index altogether, and just scan the Clustered index.

-Chad

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-09-14 : 12:26:19

Thought that because the CI has the data itself on the leaf level, the NCI needs to traverse the CI anyway and extract the pages, as the NCI uses pointers.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-14 : 12:28:49

In your examples the NCI has the key column (col1) and the CI key (col2). So as long as those are the only 2 columns required there would be no need to use the CI.

-Chad

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-09-14 : 16:32:32

quote:
Originally posted by chadmat

In your examples the NCI has the key column (col1) and the CI key (col2). So as long as those are the only 2 columns required there would be no need to use the CI.

-Chad

Got it!

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources