Press "Enter" to skip to content

Using Index Column Order and Dynamic SQL to Fix Non-SARGable Queries

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.