Post reply

How do I use the Index instead of a TableScan

  • November 10, 2002 at 5:59 pm

    #79861

    Hi,

    I have a code similar to this.

    Select Field1, Field2 From Table1 Where

    UCase(Field1) = 'MYTEXT'

    I have a Primary Ket Index defined on the Field1 Column. But the following code seems to use a TableScan instead of the defined Index. Is there anyway I can define the use of the Index or rewrite the SQL.

    Best Regards,

    Trevor Benedict R

    Microsoft Certified Professional (VB)


    Best Regards,

    Trevor Benedict R
    Microsoft Certified Solution Developer

  • November 10, 2002 at 6:41 pm

    #440924

    If SQL uses a table scan then the optimizer decided not to use the index. You could force sql to use the index with hints but i don't recommend it.

  • November 11, 2002 at 7:22 am

    #440948

    I've found that applying a function such as "upper()" on an indexed column in the where clause causes the optimizer to disregard the index. If you're server is not case-sensitive remove the upper() function as it is not necessary, anyway. If you indeed <b>must</b> force lookups to use upper() or lower(), I'm afraid you'll have to live with the slower performance.

    Steve Armistead,

    Database Administration

    Panther Systems Northwest, Inc.

    Vancouver, WA

    Steve

  • November 11, 2002 at 4:14 pm

    #441049

    If you use any function on a column then an index cannot be used.

    You have 2 options

    create an computed column on the table and index that.

    create an indexed view

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 11, 2002 at 5:12 pm

    #441062

    Thanks for the ideas. Simon, I think I would really benefit from the Indexed View for the timebeing.

    Best Regards,

    Trevor Benedict R

    Microsoft Certified Professional (VB)


    Best Regards,

    Trevor Benedict R
    Microsoft Certified Solution Developer

  • November 11, 2002 at 6:17 pm

    #441068

    Remember only available in enterprise edition

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 6 posts - 1 through 5 (of 5 total)

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