Code Question

I have this in the Code section under Report->Properties:

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
Return CStr(Fields!GM.Value)

Case "STYLECODE"
Return Fields!STYLECODE.Value

Case "CUSTOMER"
Return Fields!CUSTOMER.Value

End Select
End Function

I have a call to Code.GetSort() in the sorting tab of the report data table.

I get this error when I build:

Error 1 [rsCompilerErrorInCode] There is an error on line 4 of custom code: [BC30469] Reference to a non-shared member requires an object reference.

i think the problem is in the code body can't use report parameters.
so the code maybe is:
public Function GetSort(SortType as String) As String
Select Case SortType

then use it like GetSort(Report.Parameters!SortBy.Value) in the expression.
|||

Just pass the paremeter value to the function in the code and use that inside your code like this

Public Function GetSort(SortBy as String) As String

Select Case SortBy

........

End Function

Shyam

|||

Hi Sam,

You have to use GetSort= instead of Return (VB not accepts return keyword)

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
GetSort=CStr(Fields!GM.Value)

Case "STYLECODE"
GetSort= Fields!STYLECODE.Value

Case "CUSTOMER"
GetSort= Fields!CUSTOMER.Value

End Select
End Function

Regards,

Nanda kumar R

|||

Mr Nanda Kumar,

Please get your basics right. You can use Return keyword in the code in report.

The error is because Parameter is being referenced in the code the way it is referenced in expressions.

Shyam

|||Looks like we all need to get our basics right.
Problem was not that the parameter was referenced in the body, but that fields were referenced in the header. So now I have this in Report->Properties ->Code:

Public Function GetSort(sortby As String, sc As String, cu As String) As String
Select Case sortby

Case "STYLECODE"
Return sc

Case "CUSTOMER"
Return cu

End Select
End Function

And this in the sort tab of the data table properties:

=IIF(Parameters!SortBy.Value="GM",
Fields!GM.Value,
Code.GetSort(Parameters!SortBy.Value,Fields!STYLECODE.Value,Fields!CUSTOMER.Value))

I really wanted to avoid a mind bending iif statement hence the desire for the select case statement in the code. The GM field is numeric and it does not sort as a string so I have to handle that one separately.

|||I have another question if you all will so indulge me: In Visual Studio Report Designer, Click File-> New -> New File -> VBScript File.

If I create a code file this way, how do I call a function in it from the report?

Thanks
|||

Dont you get the same error when you use Parameters!something.Value in code?

It will give that error!!!!!!

You can only add reference to .dll assemblies and use the methods in those assemblies.

Shyam

|||>>You can only add reference to .dll assemblies and use the methods in those assemblies.

Sorry for asking silly questions but what is the purpose of the vbscript file capability if you cant call functions in your script?

|||

For SSRS, the referenced code should be pre-compiled but vbscript can only be compiled to an exe or interpreted at runtime.

You can transform your vbscript code to .net code and place it under code section directly.

Can u please mark the previous issue as marked?

Shyam

Code Question

I have this in the Code section under Report->Properties:

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
Return CStr(Fields!GM.Value)

Case "STYLECODE"
Return Fields!STYLECODE.Value

Case "CUSTOMER"
Return Fields!CUSTOMER.Value

End Select
End Function

I have a call to Code.GetSort() in the sorting tab of the report data table.

I get this error when I build:

Error 1 [rsCompilerErrorInCode] There is an error on line 4 of custom code: [BC30469] Reference to a non-shared member requires an object reference.i think the problem is in the code body can't use report parameters.
so the code maybe is:
public Function GetSort(SortType as String) As String
Select Case SortType

then use it like GetSort(Report.Parameters!SortBy.Value) in the expression.
|||

Just pass the paremeter value to the function in the code and use that inside your code like this

Public Function GetSort(SortBy as String) As String

Select Case SortBy

........

End Function

Shyam

|||

Hi Sam,

You have to use GetSort= instead of Return (VB not accepts return keyword)

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
GetSort=CStr(Fields!GM.Value)

Case "STYLECODE"
GetSort= Fields!STYLECODE.Value

Case "CUSTOMER"
GetSort= Fields!CUSTOMER.Value

End Select
End Function

Regards,

Nanda kumar R

|||

Mr Nanda Kumar,

Please get your basics right. You can use Return keyword in the code in report.

The error is because Parameter is being referenced in the code the way it is referenced in expressions.

Shyam

|||Looks like we all need to get our basics right.
Problem was not that the parameter was referenced in the body, but that fields were referenced in the header. So now I have this in Report->Properties ->Code:

Public Function GetSort(sortby As String, sc As String, cu As String) As String
Select Case sortby

Case "STYLECODE"
Return sc

Case "CUSTOMER"
Return cu

End Select
End Function

And this in the sort tab of the data table properties:

=IIF(Parameters!SortBy.Value="GM",
Fields!GM.Value,
Code.GetSort(Parameters!SortBy.Value,Fields!STYLECODE.Value,Fields!CUSTOMER.Value))

I really wanted to avoid a mind bending iif statement hence the desire for the select case statement in the code. The GM field is numeric and it does not sort as a string so I have to handle that one separately.|||I have another question if you all will so indulge me: In Visual Studio Report Designer, Click File-> New -> New File -> VBScript File.

If I create a code file this way, how do I call a function in it from the report?

Thanks|||

Dont you get the same error when you use Parameters!something.Value in code?

It will give that error!!!!!!

You can only add reference to .dll assemblies and use the methods in those assemblies.

Shyam

|||>>You can only add reference to .dll assemblies and use the methods in those assemblies.

Sorry for asking silly questions but what is the purpose of the vbscript file capability if you cant call functions in your script?

|||

For SSRS, the referenced code should be pre-compiled but vbscript can only be compiled to an exe or interpreted at runtime.

You can transform your vbscript code to .net code and place it under code section directly.

Can u please mark the previous issue as marked?

Shyam

code for re-index

hi,
SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql code.
DECLARE db_cursor CURSOR
FOR
SELECT name FROM master..sysdatabases where name not like 'test%' and
dbid>4 order by 1
OPEN db_cursor
......
DECLARE @.dbname sysname
set @.sql = 'use ' + @.dbname + char(13) + 'GO'
exec ( @.SQL)
I declare table cursor to run the reindex ...
It's re-indexing the same db(first one)... don't know why?
any ideas?
ThanksHi
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
Note it uses undocumented stored procedure just be aware
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||Thanks,
How do I find out if it's running table by table... Can I print something>
Thanjks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi
> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
> Note it uses undocumented stored procedure just be aware
>
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
>> code.
>>
>> DECLARE db_cursor CURSOR
>> FOR
>> SELECT name FROM master..sysdatabases where name not like 'test%'
>> and dbid>4 order by 1
>> OPEN db_cursor
>> ......
>> DECLARE @.dbname sysname
>> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
>> exec ( @.SQL)
>> I declare table cursor to run the reindex ...
>> It's re-indexing the same db(first one)... don't know why?
>> any ideas?
>> Thanks
>|||How about below?
EXEC sp_msForEachTable @.COMMAND1= 'PRINT ''?'' DBCC DBREINDEX ( "?") PRINT '' '''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:Ol0FSnMuHHA.1052@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> How do I find out if it's running table by table... Can I print something>
>
> Thanjks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
>> Note it uses undocumented stored procedure just be aware
>>
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
>> code.
>>
>> DECLARE db_cursor CURSOR
>> FOR
>> SELECT name FROM master..sysdatabases where name not like 'test%'
>> and dbid>4 order by 1
>> OPEN db_cursor
>> ......
>> DECLARE @.dbname sysname
>> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
>> exec ( @.SQL)
>> I declare table cursor to run the reindex ...
>> It's re-indexing the same db(first one)... don't know why?
>> any ideas?
>> Thanks
>>
>|||works now--thanks a lot
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||See BOL for DBCC SHOWCONTIG. There is a script there for what you want to
do.
--
TheSQLGuru
President
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>

Code does not select any data for information from the different tables in the database

Dear list,

I am trying to get the names of the tables and the column names from the
code below for a database but it is not working. When I run the query
below the column titles are delivered but there is no data. I think this
might be a premissions issue. Has anyone run into this before?

Thanks in advance.

Use Test_db

/* Provides Table Name, Column Name, Extened Description */

Select a.name as tbl_name, b.name as column_name, d.name as data_type,
d.length as length, d.xprec as prec, d.scale as scale, b.usertype,
b.scale, c.value
from sysobjects as a inner join syscolumns as b on a.id=b.id inner join
sysproperties as c on b.colid=c.smallid and a.id=c.id
inner join systypes as d on b.xtype=d.xtypeJeff,

This query will only display rows for columns that have extended properties assigned to them with sp_addextendedproperty. If you need to
see all columns, you can use outer joins instead of inner joins.

For the record, the [sysproperties] table is undocumented and unsupported. Here's an alternative, though a messy one:

http://groups.google.com/groups?q=4...03-E3F3F8312314

Steve Kass
Drew University

Jeff Magouirk wrote:
> Dear list,
> I am trying to get the names of the tables and the column names from the
> code below for a database but it is not working. When I run the query
> below the column titles are delivered but there is no data. I think this
> might be a premissions issue. Has anyone run into this before?
> Thanks in advance.
>
>
> Use Test_db
> /* Provides Table Name, Column Name, Extened Description */
> Select a.name as tbl_name, b.name as column_name, d.name as data_type,
> d.length as length, d.xprec as prec, d.scale as scale, b.usertype,
> b.scale, c.value
> from sysobjects as a inner join syscolumns as b on a.id=b.id inner join
> sysproperties as c on b.colid=c.smallid and a.id=c.id
> inner join systypes as d on b.xtype=d.xtype
>|||Thank you for the answer, but I am new to the SQL Server world and I am
not sure how to apply the
extended properties with the sp_addextendedproperty function.

I have tried after reading the online books.

Thanks in advance.

Jeff

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Code and diff sql servers

I'm running out of ideas...
Given the following example of code:
set @.partialname = 'u'
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
Why would two different sql servers, having exactly the same data, give
different results? One server returns an empty set, while the other returns
all rows where col1 starts with 'u'. A server setting I'm guessing, but
can't find what it is.
Can you help?crud - I messed up my explanation- it works with the 'u' but not when
@.partialname is blank ('').
Its this code that works on one server, but not the other:
set @.partialname = ''
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
If @.partialname = 'u' -- both servers process correctly.
Sorry about that.
'
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I'm running out of ideas...
> Given the following example of code:
> set @.partialname = 'u'
> SELECT col1, col2, col3
> FROM tbl1
> WHERE col1 LIKE @.partialName + '%'
> ORDER BY col1
> Why would two different sql servers, having exactly the same data, give
> different results? One server returns an empty set, while the other
> returns all rows where col1 starts with 'u'. A server setting I'm
> guessing, but can't find what it is.
> Can you help?
>
>|||My guess is that the one thing you do not show - the data type of
partialname - is the problem. Is it, by any chance, CHAR(1)? If so
you are matching on ' %' when it is blank. Try making it varchar.
And you might have tried a little research of your own:
set @.partialname = ''
SELECT @.partialName + '%'
Roy
On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
wrote:

>crud - I messed up my explanation- it works with the 'u' but not when
>@.partialname is blank ('').
>Its this code that works on one server, but not the other:
>set @.partialname = ''
>SELECT col1, col2, col3
>FROM tbl1
>WHERE col1 LIKE @.partialName + '%'
>ORDER BY col1
>If @.partialname = 'u' -- both servers process correctly.
>Sorry about that.
>'
>"mikeb" <mike@.nohostanywhere.com> wrote in message
>news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
couple hours trying different options. ALL before posting.
You might want to get your crystal ball in for repair - it doesn't seem to
be working today...
@.partialname is VarChar(50)
It appears that the other database is SQL7, versus SQL2000 (which works)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
> My guess is that the one thing you do not show - the data type of
> partialname - is the problem. Is it, by any chance, CHAR(1)? If so
> you are matching on ' %' when it is blank. Try making it varchar.
> And you might have tried a little research of your own:
> set @.partialname = ''
> SELECT @.partialName + '%'
> Roy
>
> On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
> wrote:
>|||It seems that the difference was that even though @.partialName, a
varchar(50), was passed an empty string ('') to the s.proc, SQL7 somehow
converted it to a single blank char (' '). Where SQL2000 left it empty. I
could very well be doing something wrong here - I'm just trying to fix an
error in what code we were left with. Open to suggestions if its bad form.
Wow. I even kept researching after my hand was slapped for not (sic)...
pomposity gets really tiring sometimes.
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:udUKw1BNGHA.2752@.TK2MSFTNGP14.phx.gbl...
> TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
> couple hours trying different options. ALL before posting.
> You might want to get your crystal ball in for repair - it doesn't seem to
> be working today...
> @.partialname is VarChar(50)
> It appears that the other database is SQL7, versus SQL2000 (which works)
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
>|||Sorry it came across that way. My apologies.
You should be able to get around the problem with:
RTRIM(@.partialName) + '%'
Roy|||Yep, thats exactly what I did to get it working - I meant to mention that
too in the previous post. thx.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:27scv1l4fqtv3hrvuf0msr6q6elv7fl5ej@.
4ax.com...
> Sorry it came across that way. My apologies.
> You should be able to get around the problem with:
> RTRIM(@.partialName) + '%'
> Roy

Coalesce Question

Hi.
I'm using Coalesce to change a NULL returned value from a Select statement.
Here's my code:
SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FROM
MyTable WHERE RTrim(Subtable_Id) = 'I03U'
Print @.StartDate
When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
@.StartDate
printed out.
When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
get the @.StartDate printed out. I was expecting to see 01/01/1980.
What am I missing?
TIA.
RitaRitaG wrote:
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select statement
.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FRO
M
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
The problem is that if the SELECT doesn't return any rows then the
assignment will never be made. For single value assignments use SET
instead of SELECT.
SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
I assume you meant to retrieve the minimum date but in the query you
posted you specified TOP without ORDER BY so in fact your results may
be unpredictable.
The YYYYMMDD format I've used is better for dates because it's
unambiguous and doesn't depend on any of your regional connection
settings.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Coalesce works on individual columns, for each row returned by your query.
If no row is returned, then there is no column value for coalesce to work
on. Coalesce is only useful when you have rows returned, but a particular
column is null, and you want to return a default value instead of the null.
You need to either change your SQL to insure that a row is always returned
(probably not what you want to do) or check to see if any rows were returned
(which appears to be your goal anyway).
Simply checking to see if @.StartDate is null AFTER your select may
accomplish what you want.
If you post the rest of your code, folks may be able to give some better
advice. Since this code is out of context, I can only guess at what you are
doing with it.
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita|||"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
> statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
> FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
Your WHERE clause only allows data to be printed out WHERE your Subtable ID
= I03U.
The other issue I see here is the use of your COALESCE... While this should
work for what you are doing, an ISNULL(Discount_Effect_Date, '01/01/1980')
may work better for you. COALESCE is generally used to find the first
non-null value in a list of columns. For example COALESCE(payrate_salary,
payrate_daily, payrate_hourly)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks to all for your responses.
The dates are all the same that satisfy the WHERE clause so I could have
used a Distinct but I think Top 1 is faster.
The SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
will work for me. Thanks for the "YYYYMMDD format" suggestion.
Learn something every day! :-)
"David Portas" wrote:

> RitaG wrote:
> The problem is that if the SELECT doesn't return any rows then the
> assignment will never be made. For single value assignments use SET
> instead of SELECT.
> SET @.startdate =
> (SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
> FROM mytable
> WHERE RTRIM(subtable_id) = 'I03U');
> I assume you meant to retrieve the minimum date but in the query you
> posted you specified TOP without ORDER BY so in fact your results may
> be unpredictable.
> The YYYYMMDD format I've used is better for dates because it's
> unambiguous and doesn't depend on any of your regional connection
> settings.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

coalesce

Hi,
How can i display a integer value to it's description i a lookuptable? I did
something like this, but i still got the integer value.
select coalesce(myIntColumn, (select MyIntDescription from descriptions)
from payments
What is wrong with this?First of all, a parenth. is missing..

> select coalesce(myIntColumn, (select MyIntDescription from descriptions))
> from payments
Second, you should know that you are only able to get back ONE Value. If
dont know if your query mentioned above can handle this, i think it will
return more than one value.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jason" <jasonlewis@.hotrmail.com> schrieb im Newsbeitrag
news:ug%235OHlRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can i display a integer value to it's description i a lookuptable? I
> did
> something like this, but i still got the integer value.
> select coalesce(myIntColumn, (select MyIntDescription from descriptions)
> from payments
>
> What is wrong with this?
>|||coalesce isnt designed for what you aare trying to do ( check BOL )
why not just join to your lookup table ? e.g.
select MYINTDESCRIPTION from PAYMENTS join DESCRIPTIONS on
PAYMENTS.MYINTCOLUMN = DESCRIPTIONS.INTCOLUMN
I would assume that the integer value in "descriptions" table is unique, so
put a clustered primary key on that...|||I assume you want a JOIN here:
SELECT P.myIntColumn, D.myIntDescription
FROM Payments AS P
INNER JOIN Descriptions AS D
ON P.myIntColumn = D.myIntColumn
David Portas
SQL Server MVP
--|||Jason
Use Northwind
select
coalesce(( select orderid from orders where orderid = 10248),
(select top 1 orderid from [order details]
where orderid = 10249
)
)
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ug%235OHlRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can i display a integer value to it's description i a lookuptable? I
did
> something like this, but i still got the integer value.
> select coalesce(myIntColumn, (select MyIntDescription from descriptions)
> from payments
>
> What is wrong with this?
>

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/