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
|||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) 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/greg_linwood/archive/2006/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...
>
>
|||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/
|||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...
>
|||Hi Greg
I did some testing on table with 1 mln rows and it showed that IO for CI was
a little bit less . I think it very true as Kalen said that[vbcol=seagreen]
SELECT IDENTITY(INT) "Prodid" INTO Products
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE UNIQUE CLUSTERED INDEX my_CI_Products ON Products(Prodid)
SET STATISTICS IO ON
SELECT Prodid FROM Products WHERE Prodid BETWEEN 500 AND 15000
SET STATISTICS IO OFF
--Table 'Products'. Scan count 1, logical reads 27, physical reads 0,
read-ahead reads 0.
DBCC DROPCLEANBUFFERS
DROP INDEX Products.my_CI_Products
CREATE UNIQUE NONCLUSTERED INDEX IX_Products ON dbo.Products
(
Prodid
) ON [PRIMARY]
--Table 'Products'. Scan count 1, logical reads 30, physical reads 0,
read-ahead reads 0.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23Hg0PaiTHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sorry - RowIds are the issue rather than cluster keys. Gert did mention
> seeks, but he was also refering to my previous post in which I
> specifically mentioned range scans so it seems like confusion reigns in
> this thread (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23sN7WPiTHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Greg
Again we talk about CI Seek not Scans , right? In your reply to me you said
the followng

>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.
And this one means the opposite , or can you elaborate a liitle bit?

> This confirms my point that queries which access all columns are more
> efficient when range scanning via CIX than NCIX (less IO from the CIX than
> from the NCIX).
So you say that NCI seeks are faster than CI seeks for range queries , do I
understand you properly?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u$gsRdnTHHA.4956@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> This confirms my point that queries which access all columns are more
> efficient when range scanning via CIX than NCIX (less IO from the CIX than
> from the NCIX).
> It would be a mistake to conclude that a range scan against CIXs is always
> more efficient based on this superficial example though (one which is very
> commonly made). Most real world queries are more complex than selecting a
> single column from a single column table & in most (though not all) cases
> where a query is accessing a subset of the columns in a table, NCIXs will
> out-perform CIXs for range-scans.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ulaH8$mTHHA.480@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Greg
I'm sorry but I meant for "range query" using BETWEEN or AND operators as
prodid BETWEEN 10 AND 100 for example
So SQL Server does Clustered Index Seek and NOT Clustered Index Scan
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5yJVvnTHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hi Uri
> Earlier in this thread you said "CI is best for range queries".
> Range queries require scans, not seeks so I've been talking about scans,
> not seeks.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23v5h7inTHHA.4764@.TK2MSFTNGP05.phx.gbl...
>