SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index Scan


Clustered Index Scan

Author
Message
ramana3327
ramana3327
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2127 Visits: 2083
Hi,
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. Please let me know what are the cause of the Index scan how can we change that to index seek?

I am giving that kind of similar query below

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '

Thanks,
Ramana
Jared Karney
Jared Karney
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5014 Visits: 3694
ramana3327 (3/28/2014)
Hi,
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. Please let me know what are the cause of the Index scan how can we change that to index seek?

I am giving that kind of similar query below

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '

Thanks,
Ramana
You really haven't provided enough information. Please look at the link referring to Jeff Moden in my signature to help us help you. That being said, I assume that since this is a product table, that the productid is the clustered key. Clustered index contains all of your data. So, since you are filtering on SalesID and it is not the first predicate in the clustered key (also assuming you have no other indexes), it has to scan the clustered index (data) to find what you are looking for. One solution (Assuming it makes sense on all levels), is to add a non-clustered index on Product(SalesID, Product) and include the productID column.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64431 Visits: 19117
First, don't use NOLOCK. It's a poor practice.

Second, an index scan occurs when the query optimizer thinks that this is the fastest way to find data. Without an index that works better, a scan occurs. If you want to avoid a scan, you'll likely need some index on Product or SalesID. Also, your SalesID value looks wrong. Are you somehow building dynamic SQL?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15434 Visits: 18620
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '


Apart from not being SARGable, this query puzzles me, would you ever have a SalesID value of "@salesId"? or Product = "Clothes " with a trailing space? Rolleyes

As Steve has already mentioned, the dirty read / nolock, I'm not going to overstate the obvious there, but still ask the question, why using this hint? This is something that is justifiable, only if there is a specific problem, not as a blanket protection!

Back to the question, the optimizer may deem an index scan to be less work than other options. This can happen if the table is small, indexes are fragmented or not applicable and so on. To fully work out 1) is it bad 2) can it be fixed, one has to have more information, as Jared said earlier.

Hope this helps.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89317 Visits: 45284
Eirikur Eiriksson (3/28/2014)
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '


Apart from not being SARGable...


All the predicates in that query are SARGable. They're all direct comparisons of a column to an expression (a nonsensical one in one case)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15434 Visits: 18620
GilaMonster (3/28/2014)
Eirikur Eiriksson (3/28/2014)
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '


Apart from not being SARGable...


All the predicates in that query are SARGable. They're all direct comparisons of a column to an expression (a nonsensical one in one case)


I may have wrongly jumped to a conclusion here, correct me if I'm wrong, but I think the statement is not SARGable because of Data Type Precedence (MSN: When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.)
My train of thought was that an "id" value would be numeric.
If I am wrong, then sorry;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89317 Visits: 45284
INT has a higher precedence than string, the string will convert to int, so if that is an int column (which we don't know), then the query will be essentially

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= CONVERT_IMPLICIT(INT, '@salesId') and Product = 'Clothes '



Which is SARGable and going to fail with a conversion error.

If you don't believe me, try running this:

IF ('This is a string' = 1)
print 'True'



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15434 Visits: 18620
GilaMonster (3/28/2014)
INT has a higher precedence than string, the string will convert to int, so if that is an int column (which we don't know), then the query will be essentially

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= CONVERT_IMPLICIT(INT, '@salesId') and Product = 'Clothes '



Which is SARGable and going to fail with a conversion error.

If you don't believe me, try running this:

IF ('This is a string' = 1)
print 'True'


Guess I'm guilty here:-P neither do I know the data type of the column nor the variable (being passed as char), have to learn to think before talk....w00t

Still leaves the original questions unanswered.

Thanks Gail for correcting my wrong her!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search