How to make T-SQL query SARGable?

Question

How to make T-SQL query SARGable?

imran.adam

Ten Centuries

Points: 1008
More actions
February 13, 2014 at 5:03 am

#304408

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

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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)

imran.adam Ten Centuries Points: 1008 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

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)

imran.adam Ten Centuries Points: 1008 More actions · Answer 4

imran.adam

Ten Centuries

Points: 1008

February 13, 2014 at 1:21 pm

#1688912

Thanks ill give that a shot