Clustered versus Nonclustered

Hi,
I have one easy question for u all:
1) If I run a below query on table with clustered index on Productid column
Select productid from products where productid between 10 and 20 order by
Productid
2) If I run a below query on table with nonclustered index on Productid
column and no other index on table
Select productid from products where productid between 10 and 20 order by
Productid
Thn, which will run faster and why?
Thanks in advance
Manu Jaidka"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
I doubt there would be much difference at all. If you have a second column
though, like ProductName, that's not part of either index and you run a
query like this:
Select productid, productname from products where productid between 10 and
20 order by Productid
You'll probably get better performance out of the clustered index since it
eliminates the bookmark lookups. Why not try it for yourself and look at
the query execution plans?|||In this particular case, the query would run faster with a nonclustered
index on the productid column, because the index "covers" the query, so
there is no need to retrieve the data row. If your select list contained
other columns that aren't covered by the index, then a clustered index would
probably be faster.
--Rob Roberts
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
> Thanks in advance
> Manu Jaidka|||manu
CI is best for range queries
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
> Thanks in advance
> Manu Jaidka|||Non-Clustered indexes (assuming they cover the query such as this case) are
faster than Clustered Indexes for range scans, other than in a few
relatively obscure cases. This is because fewer pages need to be scanned,
read from memory & locked with a NCIX vs a CIX.
I've blogged about this here:
http://blogs.sqlserver.org.au/blogs.../09/11/365.aspx
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
> manu
> CI is best for range queries
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
>|||Hi,
Here is what I found after doing a lot of R&D on this:
Logical reads are less in case of clustered index as compared to non
clustered index.
Please let me know what can be inferred from above results?
Thanks
Manu Jaidka
"Greg Linwood" wrote:

> Non-Clustered indexes (assuming they cover the query such as this case) ar
e
> faster than Clustered Indexes for range scans, other than in a few
> relatively obscure cases. This is because fewer pages need to be scanned,
> read from memory & locked with a NCIX vs a CIX.
> I've blogged about this here:
> http://blogs.sqlserver.org.au/blogs...gs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
>|||On Feb 9, 1:18 pm, manu <m...@.discussions.microsoft.com> wrote:
> Hi,
> Here is what I found after doing a lot of R&D on this:
> Logical reads are less in case of clustered index as compared to non
> clustered index.
> Please let me know what can be inferred from above results?
> Thanks
> Manu Jaidka
>
> "Greg Linwood" wrote:
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
What can be inferred from above results? First of all, your conclusion
is not correct. If you post how you came to this conclusion, we can
help you find your errors. Have you read Greg Linwood's blog? Also
read about index covering here:
www.devx.com/dbzone/Article/29530
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Hi Greg,
Do you have any information on these rare cases? Because I can't image
why a covering nonclustered index seek would be slower than a clustered
index seek...
Gert-Jan
Greg Linwood wrote:[vbcol=seagreen]
> Non-Clustered indexes (assuming they cover the query such as this case) ar
e
> faster than Clustered Indexes for range scans, other than in a few
> relatively obscure cases. This is because fewer pages need to be scanned,
> read from memory & locked with a NCIX vs a CIX.
> I've blogged about this here:
> http://blogs.sqlserver.org.au/blogs...gs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
[snip]|||Hi Gert
NCIXs have the burden of carrying RowIds or Cluster Keys. Range scans within
queries which access all (or sometimes nearly all) columns in a table will
be more efficient (less reads / locks) if the filter matches a CIX than a
NCIX as the rows will be packed more densely into the CIX than the NCIX. The
CIX doesn't carry the extra RowId / Cluster Key so you get more rows per IO
& therefore less IOs are required.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:45CF780A.CA908D8C@.toomuchspamalready.nl...
> Hi Greg,
> Do you have any information on these rare cases? Because I can't image
> why a covering nonclustered index seek would be slower than a clustered
> index seek...
> Gert-Jan
>
> Greg Linwood wrote:
> [snip]|||If the clustered keys are part of the set of columns you are accessing, they
are already part of the ncix, and there is no 'extra' space required.
In addition, Gert-Jan asked about seeks, not scans. :-)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:em4AnDiTHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Gert
> NCIXs have the burden of carrying RowIds or Cluster Keys. Range scans
> within queries which access all (or sometimes nearly all) columns in a
> table will be more efficient (less reads / locks) if the filter matches a
> CIX than a NCIX as the rows will be packed more densely into the CIX than
> the NCIX. The CIX doesn't carry the extra RowId / Cluster Key so you get
> more rows per IO & therefore less IOs are required.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:45CF780A.CA908D8C@.toomuchspamalready.nl...
>