Clustered or NonClustered? - SQL Server Forums

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)

Clustered or NonClustered?

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2011-01-09 : 06:40:30

When I have a clustered and a nonclustered index on a table, Optimizer dicides to choose nonclustered index for this simple query : SELECT COUNT(*) FROM tbl;

By setting STATISTICS IO ON you can see the engine needs less logical reads for nonclustered index, so returning the result faster. can anybody explain why nonclustered index, when used, is faster for this query?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-09 : 07:43:43

It's smaller. The clustered is usually the largest index on the table. To get the count, SQL just needs to read through the leaf nodes of an index. Any non-filtered index will do. It'll pick the smallest because it incurs the least IOs.

--
Gail Shaw
SQL Server MVP

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2011-01-09 : 08:29:15

Thank you for your reply. but as long as my tests show it doesnt depend on the key length. for example

CREATE TABLE tbl (id int, c char(200))

CREATE CLUSTERED INDEX idx1 ON tbl (id)
CREATE INDEX idx2 ON tbl (c)

-- INSERT some records

SELECT COUNT(*) FROM tbl

despite its longer key length, optimizer chooses idx2. and i dont know why?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-09 : 09:24:36

I didn't say it depended on key length, I said it depends on the size of the index. The entire index.

The clustered index is the largest index on the table, because it has the actual data row (the entire row) at the leaf level.
SQL will use the index with the smallest number of leaf pages to do the count (not the smallest key size) because it just has to could the rows, not check values.

Now in this case the two are the same size, so it won't matter much.

--
Gail Shaw
SQL Server MVP

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2011-01-09 : 09:44:58

Hi Gail. thank you again for your reply. if you insert, say, 10000 rows in that sample table and after that executing these two queries with "Include Actual execution plan" on you will see a lot of difference, so it seems that it matter much.

SELECT COUNT(*) FROM tbl WITH (INDEX = idx1)
SELECT COUNT(*) FROM tbl WITH (INDEX = idx2) -- which is the default choice by optimizer

and for pages, as long as i know nonclustered indexes have clustered keys embeded in it, which makes it worse for me to understand why optimizer behaves like this.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-09 : 11:33:08

What do you still not understand?

For a count, SQL uses the smallest nonclustered index - the one with the fewest leaf pages - that will satisfy any where clause that's on the query. No where clause, SQL just uses the smallest of the nonclustered indexes.

It will make very little difference in this example, because the nonclustered index has all the columns of the table. Don't let the 'actual execution plan' percentages confuse you, they're estimates.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources