How to make T-SQL query SARGable?

  • Hi Experts

    I am trying to turn this query into something more SARGable (mainly the LEN(CardNumber.....) bit), any ideas? (This is only part of the query)

    SELECT ContactGUID, CardNumber, ROW_NUMBER() OVER(PARTITION BY ContactGUID ORDER BY SignedDate DESC) as row_no

    FROM Cards

    WHERE

    LEFT(CardNumber, 6) = '633176'

    AND LEN(CardNumber) IN (16, 19) AND (Status IS NULL OR Status = 'A')

    AND ContactGUID IS NOT NULL

    Any help/advice much appreciated.

    Thanks

  • Yes.

    I'm assuming that the card number is stored as a VARCHAR. Since credit card numbers cannot begin with zeros, I'd first recommend that column be changed to a BIGINT which will easily handle a 19 digit credit card number. Then, you could just do a BETWEEN of the card numbers to get the length. For example, 16 digit card numbers could be isolated with...

    WHERE CardNumber LIKE '633176%'

    AND ( CardNumber BETWEEN 1000000000000000 AND 9999999999999999 --16 digits

    OR CardNumber BETWEEN 1000000000000000000 AND 9999999999999999999 --19 digits

    )

    AND (Status IS NULL OR Status = 'A')

    AND ContactGUID IS NOT NULL

    ;

    Also notice that LIKEs that don't begin with a Wild Card are more effective than using LEFT.

    IF the CardNumber is a VARCHAR column, then the code above won't be sargable because of the datatype mismatch. If you can't change the datatype of CardNumber to BIGINT, then perhaps you could add a PERSISTED calculated column to the table for the length of the card number (which would help other similar queries, as well). That way your code could be...

    WHERE CardNumber LIKE '633176%'

    AND LenCardNumber IN (16,19)

    AND (Status IS NULL OR Status = 'A')

    AND ContactGUID IS NOT NULL

    Of course, if you made a PERSISTED calculated column for all of those conditions, life would get really easy...

    WHERE IsValid = 1

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff

    Thanks for the reply

    I like your PERSISTED column idea.

    Do you mean add another column to my cards table that is populated with a 1 if all those conditions are met? like a computed column?

    The table has approx. 11 million rows.

    Thanks

  • imran.adam (2/13/2014)


    Hi Jeff

    Thanks for the reply

    I like your PERSISTED column idea.

    Do you mean add another column to my cards table that is populated with a 1 if all those conditions are met? like a computed column?

    The table has approx. 11 million rows.

    Thanks

    The persisted column on card number length would probably be more useful overall but if the criteria that you have overall is going to be used a whole lot, then a computed column (just like you restated above) would make simplar queries lightning quick. Considering the column would only add 1 byte to each row, the ROI would be very high, indeed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks ill give that a shot

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

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