T-SQL which cause Table or Index Scan

Question

Post reply

T-SQL which cause Table or Index Scan

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
March 13, 2013 at 6:00 am

#275178

Trying to find a comprehensive list of T-SQl that will cause a table scan.
1 Such as Select * from xxx,
2 Where xyz like '%abc%' etc, etc.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jpomfret7 SSCommitted Points: 1628 More actions · Answer 1

Using a function in the where clause can change a seek to a scan:

select checkcolumn, LongColumn

from test

where round(checkcolumn,0) = 7

Note: checkcolumn is an int and there is a suitable index that is used for a seek when round() is not used in the where clause.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 2

Thanks, thats a new one for me. Is there anywhere which has a list of all these things in T-SQl that can cause scans.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's no such list. The optimiser can pick table scans even when the where clause looks like it could use an index. Has to do with % of rows returned, whether the predicates are SARGable, whether the index is covering, whether there's an order required (order by, group by, windowing function, etc) and a whole bunch of other things.

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