How to make this T-SQL more sargable?

Question

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). CardNumber is Varchar(50) and already has a index on it

SELECT ContactGUID, CardNumber, 
ROW_NUMBER() OVER(PARTITION BY ContactGUID ORDER BY SignedDate DESC) as row_no 
FROM Cards 
WHERE CardNumber LIKE '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

@veljkoz check [THIS](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) for non-SARGable and SARGable T-SQL. — Hoh, Feb 14 '14 at 08:13
Huh? You already did get an answer to this [elsewhere](http://www.sqlservercentral.com/Forums/Topic1541117-1292-1.aspx#bm1541198), so why do you ask again? — Ocaso Protal, Feb 14 '14 at 08:33
If you need to query on LEN of something, pre calculate/use computed column that so you can index on it. — Allan S. Hansen, Feb 14 '14 at 11:48
@Ocaso, reason is because I didn't get many replies on that forum — Imran Adam, Feb 14 '14 at 12:15
But you "accepted" the answer over there? And that answer looks very good. Did you try it? Would be helpful if you added some details from that answer, e.g. did you try that, did it work? I don't want to offend you, I just think that some details are very helpful here. — Ocaso Protal, Feb 14 '14 at 12:59

score 2 · Answer 1 · answered Feb 14 '14 at 08:58

So, first - like '1234%' is fast, if your index is right. Of course, this only applies if CardNumber isn't nvarchar/nchar/ntext etc. - if it is, use like N'1234%' instead.

len(CardNumber) isn't, but the engine should be smart enough to evaluate the like first. If that still leaves too many matching rows, you'll just have to have a computed column with the length of CardNumber in it, and have an index on that.

Also, in my experience, over (partition by ..., order by ...) tends to be slow.

In any case, have you actually monitored a performance problem? If so, you can check the execution plan to see where your problems might be.

score 0 · Answer 2 · answered Feb 14 '14 at 08:25

0

Have you tried using a "_" in LIKE (wildcard for single character). For example:

...
AND   (CardNumber LIKE '633176__________'
    OR CardNumber LIKE '633176___________'
    OR CardNumber LIKE '633176____________'
    OR CardNumber LIKE '633176_____________')
...

Although, I'm not sure it would actually be faster.

answered Feb 14 '14 at 08:25

veljkoz

8,384
8
55
91

This looks interesting, ill try it out. – Imran Adam Feb 14 '14 at 12:15
Actually this way is more expensive, according to the execution plan. Thanks for the reply though! – Imran Adam Feb 14 '14 at 12:31

score 0 · Answer 3 · answered Feb 14 '14 at 10:56

0

The problem of course is in the len(cardNumber)

You might try (the Cross apply functionality of T-sql)

Here is a link to a good article about this subject: http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

answered Feb 14 '14 at 10:56

user2969649

9
2

How to make this T-SQL more sargable?

3 Answers3