March 13, 2013 at 6:00 am
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.
March 13, 2013 at 6:38 am
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.
March 13, 2013 at 6:53 am
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.
March 13, 2013 at 7:11 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy