Erik Darling wraps up season 1 of Saving Sarge with a cliffhanger. First up, how setting up indexes to lead with SARGable columns makes sense:
Quite a bit, I find myself working with people who are able to change indexes, but unable to change queries.
Even making small, sane changes would nix their support, should they ask for it. I do sometimes have to laugh at the situation: if support were that great, they wouldn’t need me, and if we made the change, they probably wouldn’t need support.
Oh well, though. Keep me employed, ISVs.
Then, Erik takes a look at using dynamic SQL to solve one class of non-SARGable predicates:
The non-SARGABLE pattern that dynamic SQL helps you deal with is the the catch all query pattern, which can look like:
– col = @parameter or @parameter is null
– col = isnull(@parameter, col)
– col = coalesce(@parameter, col)Or any similar variation of null/not null checking of a parameter (or variable) in the where/join clause at runtime.
Dynamic SQL allows you to build up the specific where clause that you need for the non-NULL set of search filters.
Stay tuned for the next thrilling episode of Saving Sarge. Same SARG-time, same SARG-channel.