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/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...
> 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...
> > 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
> >
> >
>
>|||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:
> > 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/3...
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >http://blogs.sqlserver.org.au/blogs/greg_linwood
> > "Uri Dimant" <u...@.iscar.co.il> wrote in message
> >news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
> > > manu
> > > CI is best for range queries
> > > "manu" <m...@.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- Hide quoted text -
> - 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:
> 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...
> > manu
> > CI is best for range queries
[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:
>> 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...
>> > manu
>> > CI is best for range queries
> [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...
>> 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:
>> 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...
>> > manu
>> > CI is best for range queries
>> [snip]
>|||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...
> 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,
>> 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:
>> 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...
>> > manu
>> > CI is best for range queries
>> [snip]
>>
>|||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
>> 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.
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...
>> 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,
>> 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:
>> 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...
>> > manu
>> > CI is best for range queries
>> [snip]
>>
>>
>|||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 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
>> 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.
>
> 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...
>> 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,
>> 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:
>> 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...
>> > manu
>> > CI is best for range queries
>> [snip]
>>
>>
>>
>|||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 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
>> 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.
>>
>> 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...
>> 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,
>> 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:
>>>
>>> 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...
>>> > manu
>>> > CI is best for range queries
>> [snip]
>>
>>
>>
>>
>|||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...
> 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 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
>> 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.
>>
>> 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...
>> 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,
>>>
>>> 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:
>>>
>>> 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...
>>> > manu
>>> > CI is best for range queries
>>> [snip]
>>
>>
>>
>>
>>
>|||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...
>> 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 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
>> 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.
>>
>> 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...
>> 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,
>>>
>>> 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:
>>>
>>> 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...
>>> > manu
>>> > CI is best for range queries
>>> [snip]
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi Uri
Range scans are displayed as Seek icons, so it's easy to get these terms
confused. SQL Server first performs a seek, to find where it will begin the
range scan & then scans from that point, identifying which rows meet the
filter criteria before completing the "Seek" operation.
If you look closely at the execution plan you'll notice that, although
there's a "Seek" icon in the plan, it also states "Scan a particular range
of rows..." or similar at the heading of the mouse-over view of the icon.
The properties dialog also should show the "Start Range" & "End Range"
attributes of the Seek Predicates.
I tend to refer to queries which filter with predicates against unique keys
as seeks & those that filter any other way as 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:e8SP4ynTHHA.5108@.TK2MSFTNGP06.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...
>> 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 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
>>> 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.
>>
>> 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...
>>> 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,
>>>
>>> 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:
>>>>
>>>> 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...
>>>> > manu
>>>> > CI is best for range queries
>>> [snip]
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi Greg
> If you look closely at the execution plan you'll notice that, although
> there's a "Seek" icon in the plan, it also states "Scan a particular range
> of rows..." or similar at the heading of the mouse-over view of the icon.
> The properties dialog also should show the "Start Range" & "End Range"
> attributes of the Seek Predicates.
Well , I see the statement and I must admit it is really confused
I have read this article to get impression that CI it is better to use
clustered indexes to solve queries asking for ranges of data, not one row.
(Perhpas it does not really relate to our discussion)
http://www.sql-server-performance.com/gv_index_data_structures.asp
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:O2yLu9nTHHA.3948@.TK2MSFTNGP05.phx.gbl...
> Hi Uri
> Range scans are displayed as Seek icons, so it's easy to get these terms
> confused. SQL Server first performs a seek, to find where it will begin
> the range scan & then scans from that point, identifying which rows meet
> the filter criteria before completing the "Seek" operation.
> If you look closely at the execution plan you'll notice that, although
> there's a "Seek" icon in the plan, it also states "Scan a particular range
> of rows..." or similar at the heading of the mouse-over view of the icon.
> The properties dialog also should show the "Start Range" & "End Range"
> attributes of the Seek Predicates.
> I tend to refer to queries which filter with predicates against unique
> keys as seeks & those that filter any other way as 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:e8SP4ynTHHA.5108@.TK2MSFTNGP06.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...
>> 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 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
>>> 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.
>>
>> 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...
>>> 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,
>>>>
>>>> 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:
>>>>
>>>> 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...
>>>> > manu
>>>> > CI is best for range queries
>>>> [snip]
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi Uri
I didn't thoroughly read the article, but I couldn't locate any discussion
about the inefficiency associated with range-scanning CIXs so it apparently
doesn't cover the topic thoroughly.
Because CIXs contain ALL columns from the base table in their leaf pages,
they require more IO to resolve any query unless all columns are being
accessed by that query. The columns not being referenced by the query are
still being read into memory buffers, requiring more memory to process &
even worse, more locks.
Here are some good guidelines to work with for range queries:
a) If the query accesses all columns, a CIX is ideal.
b) The fewer columns accessed from the table, the more advantegeous a
covering NCIX will be over a CIX as it will require fewer page reads, less
memory & fewer locks.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23NWOxNoTHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Greg
>> If you look closely at the execution plan you'll notice that, although
>> there's a "Seek" icon in the plan, it also states "Scan a particular
>> range of rows..." or similar at the heading of the mouse-over view of the
>> icon. The properties dialog also should show the "Start Range" & "End
>> Range" attributes of the Seek Predicates.
> Well , I see the statement and I must admit it is really confused
> I have read this article to get impression that CI it is better to use
> clustered indexes to solve queries asking for ranges of data, not one row.
> (Perhpas it does not really relate to our discussion)
> http://www.sql-server-performance.com/gv_index_data_structures.asp
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:O2yLu9nTHHA.3948@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri
>> Range scans are displayed as Seek icons, so it's easy to get these terms
>> confused. SQL Server first performs a seek, to find where it will begin
>> the range scan & then scans from that point, identifying which rows meet
>> the filter criteria before completing the "Seek" operation.
>> If you look closely at the execution plan you'll notice that, although
>> there's a "Seek" icon in the plan, it also states "Scan a particular
>> range of rows..." or similar at the heading of the mouse-over view of the
>> icon. The properties dialog also should show the "Start Range" & "End
>> Range" attributes of the Seek Predicates.
>> I tend to refer to queries which filter with predicates against unique
>> keys as seeks & those that filter any other way as 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:e8SP4ynTHHA.5108@.TK2MSFTNGP06.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...
>> 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 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
>>> 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.
>>>
>>>
>>> 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...
>>> 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,
>>>>
>>>> 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:
>>>>
>>>> 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...
>>>> > manu
>>>> > CI is best for range queries
>>>> [snip]
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||I see. Thanks for the info.
Gert-Jan
Greg Linwood wrote:
> 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...
> > 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,
> >>
> >> 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:
> >>
> >> 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...
> >> > manu
> >> > CI is best for range queries
> >> [snip]
> >>
> >>
> >
> >|||On Feb 12, 2:54 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi Greg
> > If you look closely at the execution plan you'll notice that, although
> > there's a "Seek" icon in the plan, it also states "Scan a particular range
> > of rows..." or similar at the heading of the mouse-over view of the icon.
> > The properties dialog also should show the "Start Range" & "End Range"
> > attributes of the Seek Predicates.
> Well , I see the statement and I must admit it is really confused
> I have read this article to get impression that CI it is better to use
> clustered indexes to solve queries asking for ranges of data, not one row.
> (Perhpas it does not really relate to our discussion)http://www.sql-server-performance.com/gv_index_data_structures.asp
> "Greg Linwood" <g_linw...@.hotmail.com> wrote in message
> news:O2yLu9nTHHA.3948@.TK2MSFTNGP05.phx.gbl...
>
> > Hi Uri
> > Range scans are displayed as Seek icons, so it's easy to get these terms
> > confused. SQL Server first performs a seek, to find where it will begin
> > the range scan & then scans from that point, identifying which rows meet
> > the filter criteria before completing the "Seek" operation.
> > If you look closely at the execution plan you'll notice that, although
> > there's a "Seek" icon in the plan, it also states "Scan a particular range
> > of rows..." or similar at the heading of the mouse-over view of the icon.
> > The properties dialog also should show the "Start Range" & "End Range"
> > attributes of the Seek Predicates.
> > I tend to refer to queries which filter with predicates against unique
> > keys as seeks & those that filter any other way as range scans..
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >http://blogs.sqlserver.org.au/blogs/greg_linwood
> > "Uri Dimant" <u...@.iscar.co.il> wrote in message
> >news:e8SP4ynTHHA.5108@.TK2MSFTNGP06.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_linw...@.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" <u...@.iscar.co.il> wrote in message
> >>news:%23v5h7inTHHA.4764@.TK2MSFTNGP05.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_linw...@.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" <u...@.iscar.co.il> wrote in message
> >>news:ulaH8$mTHHA.480@.TK2MSFTNGP02.phx.gbl...
> >> 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
> >>> 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.
> >> 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_linw...@.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...
> >>> 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_linw...@.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" <s...@.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:
> >>>> 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/3...
> >>>> Regards,
> >>>> Greg Linwood
> >>>> SQL Server MVP
> >>>>http://blogs.sqlserver.org.au/blogs/greg_linwood
> >>>> "Uri Dimant" <u...@.iscar.co.il> wrote in message
> >>>>news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
> >>>> > manu
> >>>> > CI is best for range queries
> >>>> [snip]- Hide quoted text -
> - Show quoted text -
Well, at the very end of that article the author explicitly claims
that "Because, the leaf level of the clustered index is the data. It
should be used to save many I/Os. So, it is better to use clustered
indexes to solve queries asking for ranges of data, not one row.
For example, to search for an employee ID (empid between 3 and 9) in a
table that has a clustered index on the empid column.
Select EmpID, EmpName From DummyTable1 WHERE EMPID Between 3 And 9"
However, the author only considers a very small table, just a few
(less than 10!) rows, does no benchmarking at all. As such, I think
the author's recommendations are not relevant
to queries against large tables. Also the author does not consider
covering indexes as an alternative to clustering ones, I have no idea
why.
I disagree with the author's recommendation.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/