T-SQL which cause Table or Index Scan

  • 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.

  • 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.

  • 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.

  • 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

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

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