Post reply

Creating indexes for sum of two fields

  • May 29, 2009 at 1:28 am

    #204636

    Hi,

    The following query always uses table scan even I create index on a, b and a+b.

    Is there a way to index a+b without changing the table definition (because it is a production db, I cannot add new fields, computed columns, views etc. just creating indexes and using TSQL)?

    SELECT a, b FROM t WHERE a+b = 500

    Here is the table definition, I inserted 20000 records with random numbers.

    CREATE TABLE [dbo].[t](

    [a] [int] NOT NULL,

    [int] NOT NULL

    )

  • May 29, 2009 at 2:15 am

    #1001924

    endo64 (5/29/2009)

    The following query always uses table scan even I create index on a, b and a+b.

    Yup. Functions of any form prevent index usage

    Is there a way to index a+b without changing the table definition (because it is a production db, I cannot add new fields, computed columns, views etc. just creating indexes and using TSQL)?

    Nope. You'd have to add a persisted computed column and index that

    SELECT a, b FROM t WHERE a+b = 500

    You may get some benefit from changing the query around

    SELECT a, b FROM t WHERE a = 500 - b

    or

    SELECT a, b FROM t WHERE b = 500 - a

    Try those. They're at least partially SARGable where the original query wasn't.

    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

  • May 29, 2009 at 2:46 am

    #1001933

    Thanks for the reply, I guess no chance without adding new/computed columns.

    Even constant values prevents using the index.

    select * from t where a + 1 = 500

    a is indexed, but query scans the table anyway.

    same result with "select * from t where a = 500 - b"

  • May 29, 2009 at 2:58 am

    #1001935

    endo64 (5/29/2009)

    Even constant values prevents using the index.

    Yes it will. As I said, functions of any form prevent index seeks.

    same result with "select * from t where a = 500 - b"

    Yeah. Thinking about it, that's not sargable either because SQL doesn't know what value to seek on.

    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