Clustered DB Server: below

Showing posts with label below. Show all posts

Monday, March 19, 2012

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Why do i get the error below:
> >
> > Server: Msg 446, Level 16, State 9, Line 1
> > Cannot resolve collation conflict for equal to operation.
> >
> > What should i do?
> >
> >
> >
> >
> >
> > INSERT INTO tkl_proposals(
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, transfer_date, from_server)
> > SELECT
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, getdate(), 'KIRMIZI'
> > FROM MD_F.MidasLocal.dbo.tkl_proposals b
> > WHERE
> > (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> > b.proposal_id=a.proposal_id AND
> > b.oc_initial=a.oc_initial AND
> > b.oc_place=a.oc_place)
> > )
> > AND b.oc_initial='MD'
> > AND b.oc_place='F'
> > AND b.proposal_date >= '1-1-2005'
> >
> >
>

Sunday, March 11, 2012

Code.SafeDivide Expression

What does =Code.SafeDivide mean in an expression? I have an example below:
=Code.SafeDivide (Sum(Fields!revenue.Value), Sum(Fields!volume.Value))
Thanks, DeborahThe only time I've seen something like that is when you write a custom
code function called "SafeDivide". This will do a check on the divisor
(the second parameter) and, if it is 0, it will not perform the
division. It will just return 0. Here is an example I found (this
example will actually take a 3rd parameter - the "value if undefined"
parameter):
The following was snipped from this thread. Read it to see the full
explanation:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/b66002620ec40e52/ed9544519ff38234?q=safedivide&rnum=2#ed9544519ff38234
Public Function SafeDivide(pi_dblNumerator As Double, pi_dblDenominator
As
Double, pi_dblUndefined As Double)
If pi_dblDenominator = 0 Then
SafeDivide = pi_dblUndefined
Else
SafeDivide = pi_dblNumerator / pi_dblDenominator
End If
End Function
Regards,
Dan

Thursday, March 8, 2012

Code Problem - Updating parameter

I want to update a parameter in order to know whether a certain type of row
has been printed so that I can suppress it later. the code below generates
an error " Reference to a non-shared member requires an object reference" .
How do I pass in /reference my parameter so that I can set the value? Any
help greatly appreciated
Public Function PrintCensus() As Integer
if Parameters!PrintCensus.Value = 0 Then
Parameters!PrintCensus.Value = 1
Return 0
end if
return 1
End FunctionWhat you have to do is:
1. Define your function as:
> Public Function PrintCensus(PrintCensusValue as integer) As Integer
>
> if PrintCensusValue= 0 Then
> Return 1
> else
Return 0
> end if
> End Function
2. Call your function from expresion field as:
=Code.PrintCensus(Parameters!PrintCensus.Value)
Hope this helps,
Mónica
"Peter Feakins" <PeterFeakins@.discussions.microsoft.com> escribió en el
mensaje news:71D8DF3A-A67F-41CB-8B8B-CE71E0FF63E4@.microsoft.com...
>I want to update a parameter in order to know whether a certain type of row
> has been printed so that I can suppress it later. the code below
> generates
> an error " Reference to a non-shared member requires an object reference"
> .
> How do I pass in /reference my parameter so that I can set the value? Any
> help greatly appreciated
> Public Function PrintCensus() As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 1
> Return 0
> end if
> return 1
> End Function|||apparently you cannot change the value of a parameter after it is set prior
to report execution.
"Peter Feakins" wrote:
> I want to update a parameter in order to know whether a certain type of row
> has been printed so that I can suppress it later. the code below generates
> an error " Reference to a non-shared member requires an object reference" .
> How do I pass in /reference my parameter so that I can set the value? Any
> help greatly appreciated
> Public Function PrintCensus() As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 1
> Return 0
> end if
> return 1
> End Function

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!

Wednesday, March 7, 2012

Code a function to return a dataset in which there are two tables and relationship

I used a function to create dataset as below:

PublicFunction GetSQLDataSet(ByVal SQLAsString)As DataSet

.....

MyConnection =

New SqlConnection(MyConnectionString)

MyCommand =

New SqlCommand(SQL, MyConnection)

MyDataSet =

New DataSet

MySQLDataAdapter =

New SqlDataAdapter(MyCommand)

MySQLDataAdapter.Fill(MyDataSet)

.....

End function

It works fine.

How to code a function to return a dataset in which there are two tables and relationship?

You can use a SqlCommand which returns 2 tables--that can be a stored procedure which returns 2 tables, or a batch contains 2 SELECT commands seperated by ';' (e.g. "select * from t1; select * from t2). Then when you use the SqlDataAdapter to fill the DataSet, the DataSet will contain 2 tables. Then you can add relationships between the 2 tables as you like, you can refer to:

http://msdn2.microsoft.com/en-us/library/ay82azad.aspx

Code - Integer, Nothing vs Zero

The piece of code below attempts to convert minutes to a time string.
The problem I am having is if the value of Minutes is NULL, it treats it as
zero.
Has anyone got any suggestions ?
Thanks
Steve
---
Public Function MinutesToTime(ByVal Minutes AS Integer)
On Error GoTo errorhandler
Dim mins
Dim hrs
If IsNothing(Minutes) Then
MinutesToTime = ""
Else
hrs = Fix(Minutes / 60)
mins = Minutes - (hrs * 60)
If mins < 10 Then mins = "0" & mins
MinutesToTime = hrs & ":" & mins
End If
Exit Function
errorhandler:
MinutesToTime = "##:##"
End FunctionFirst off this is a function, you should be explicit on your return values.
You have two DIM's without specifing the type... then you have a check for
NULL (if nothing) you return a string... so which is it?
What happens if the minutes is actually 0 - what would you return? Would it
be an empty string or "00:00" ' If that is the case, then why not, for NULL
values, return "00:00" - the same as if you specified 0 minutes?
=-Chris
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> The piece of code below attempts to convert minutes to a time string.
> The problem I am having is if the value of Minutes is NULL, it treats it
> as
> zero.
> Has anyone got any suggestions ?
> Thanks
> Steve
> ---
> Public Function MinutesToTime(ByVal Minutes AS Integer)
> On Error GoTo errorhandler
> Dim mins
> Dim hrs
> If IsNothing(Minutes) Then
> MinutesToTime = ""
> Else
> hrs = Fix(Minutes / 60)
> mins = Minutes - (hrs * 60)
> If mins < 10 Then mins = "0" & mins
> MinutesToTime = hrs & ":" & mins
> End If
> Exit Function
> errorhandler:
> MinutesToTime = "##:##"
> End Function
>|||Thanks Chris for your reply
I am wanting to pass in an integer representing minutes and return a string
that looks like a time as per the following examples.
67 returns "1:07"
0 returns "0:00"
NULL returns ""
My problems is that when NULL is passed in I am currently getting "0:00"
instead of "".
I have tried using IsNull function but I get a compilation error "Name
'IsNull' is not declared."
"Chris Conner" wrote:
> First off this is a function, you should be explicit on your return values.
> You have two DIM's without specifing the type... then you have a check for
> NULL (if nothing) you return a string... so which is it?
> What happens if the minutes is actually 0 - what would you return? Would it
> be an empty string or "00:00" ' If that is the case, then why not, for NULL
> values, return "00:00" - the same as if you specified 0 minutes?
> =-Chris
> "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > The piece of code below attempts to convert minutes to a time string.
> >
> > The problem I am having is if the value of Minutes is NULL, it treats it
> > as
> > zero.
> >
> > Has anyone got any suggestions ?
> >
> > Thanks
> > Steve
> > ---
> >
> > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > On Error GoTo errorhandler
> >
> > Dim mins
> > Dim hrs
> >
> > If IsNothing(Minutes) Then
> > MinutesToTime = ""
> > Else
> > hrs = Fix(Minutes / 60)
> > mins = Minutes - (hrs * 60)
> >
> > If mins < 10 Then mins = "0" & mins
> >
> > MinutesToTime = hrs & ":" & mins
> > End If
> >
> > Exit Function
> >
> > errorhandler:
> > MinutesToTime = "##:##"
> >
> > End Function
> >
>
>|||Reporting Services doesn't support 'code reuse'
so I would reccomend just doing this in Microsoft Access; it would be a
simple format string there; right?
-Aaron
SteveH wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >|||Finally found a solution and thought I would post it here for anyone who has
the same problem.
datatype needed to be Nullable(Of Integer) instead of Integer
Public Function MinutesToTime(ByVal Minutes As Nullabe(Of Integer)) AS
String
then use HasValue instead of IsNothing
If Minutes.HasValue Then
and Minutes.Value
"SteveH" wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >

Coalescing data from more than 2 tables

I have a query, below, -Can anyone tell me how I could modify this to
add data from third and fourth tables. Many Thanks
----
select
coalesce (x.NHPART, y.TPROD)
, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
, isnull(y.TQTY,0) as "TQTY (ITH)"
from
(
select
NHPART
, isnull(sum (NCQNTY),0) NCQNTY
from
NCM
WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
='5220'
group by
NHPART
)
x full join
(
select
TPROD
, isnull(sum (TQTY),0) TQTY
from
ITH
WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
group by
TPROD
) y on x.NHPART = y.TPROD
ORDER BY coalesce (x.NHPART, y.TPROD)
----By the time I get to a three way (or worse, four way) full outer join
I find all the equality tests start to get overwhelming - to the point
I have found it hard to be sure my code is written properly.
Eventually I found an alternative way to code them that I, at least,
find much simpler.
I start by writing a query that UNIONs together just the keys - which
is to say the columns used for the IN clause comparisons - from all
the data sources.
select NHPART
from NCM
where NHSET ='INSP'
and NHCDAT between 20060201
and 20060228
and HVNDR >='5220'
UNION
select TPROD
from ITH
WHERE TTDTE between 20060201
and 20060228
and TVEND ='5220'
UNION
select OTHERPROD
from OTHERTBL
WHERE OTHERCOLUMN = 'test value'
Note that UNION performs a DISTINCT, which we need.
Now, with that working, I use that as a derived table, and make it the
root table in a LEFT outer join rather than a FULL outer join:
select K.EitherPart,
coalesce(X.NCQNTY,0) as "NCQNTY (NCM)",
coalesce(Y.TQTY,0) as "TQTY (ITH)",
coalesce(Z.SOMENUMBER) as OtherQty
from (<query from above> ) as K
LEFT OUTER
JOIN (<your first derived table> ) as X
ON K.EitherPart = X.NHPART
LEFT OUTER
JOIN (<your second derived table> ) as Y
ON K.EitherPart = Y.TPROD
LEFT OUTER
JOIN (<yet another derived table> ) as Z
ON K.EitherPart = Z.OTHERPROD
I hope this helps.
Roy Harvey
Beacon Falls, CT
On 7 Mar 2006 03:16:37 -0800, "philipbennett25" <pbennett@.xyratex.com>
wrote:

>I have a query, below, -Can anyone tell me how I could modify this to
>add data from third and fourth tables. Many Thanks
>----
>select
> coalesce (x.NHPART, y.TPROD)
>, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
>, isnull(y.TQTY,0) as "TQTY (ITH)"
>from
>(
>select
> NHPART
>, isnull(sum (NCQNTY),0) NCQNTY
>from
> NCM
>WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
>='5220'
>group by
> NHPART
> )
>x full join
>(
>select
> TPROD
>, isnull(sum (TQTY),0) TQTY
>from
> ITH
>WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
>group by
> TPROD
> ) y on x.NHPART = y.TPROD
>ORDER BY coalesce (x.NHPART, y.TPROD)
>----

Saturday, February 25, 2012

Coalesce / Comma Delimitted List

I'm trying to return multiple column, one of which is a column + a comma delimitted list of values. Below is a simplified, non working, query:

(assume @.MyBit, @.MyVarChar, and @.MyValue are all declared)

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2,
FROM TableMain

I get an ADO error: Incorrect syntax near '='

Thoughts?

Thanks(select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue)

use "[ ]" brackets instead of "( )" around the above select statment.

(and remove the last comma before the FROM stmnt)|||I now have:

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + [select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue] ELSE '' END AS Column2
FROM TableMain

and I get:
ADO error: Invalid column name 'select @.MyVarChar = COALESCE(Column3 + ',', '') from TableSub where Value = @.MyValue'

Thanks|||my apologies - here is the correct query

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2
FROM TableMain

put the "( )" back on and remove the @.MyVarChar = (that is where the err is)
you just need select coalesce...

Thursday, February 16, 2012

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/

Monday, March 19, 2012

COLLATE problem

Sunday, March 11, 2012

Code.SafeDivide Expression

Thursday, March 8, 2012

Code Problem - Updating parameter

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

Wednesday, March 7, 2012

Code a function to return a dataset in which there are two tables and relationship

Code - Integer, Nothing vs Zero

Coalescing data from more than 2 tables

Saturday, February 25, 2012

Coalesce / Comma Delimitted List

Thursday, February 16, 2012

Clustered versus Nonclustered

Clustered versus Nonclustered

Clustered versus Nonclustered

Clustered DB Server

Blog Archive

About Me