Sargable predicates and NULLs in SQL Server

Convert non-sargable predicates to sargable predicates

In this article I will talk about the concept known as SARGability, enriching it with some considerations about NULLs. I had the opportunity to study sargable predicates during a session held by Itzik Ben-Gan which inspired me.

Wikipedia defines SARGability in this way: “In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE. A query failing to be sargable is known as a non-sargable query and typically has a negative effect on query time, so one of the steps in query optimization is to convert them to be sargable. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.”

Why is it important writing sargable queries? The execution time of a query can be considered proportional to the number of pages read. Access to specific index pages reduce the likelihood of reading records affected by an active transaction, locked by an active transaction. The lower pages are read, the greater concurrency will be!

Consider the following query that extracts the purchase orders from the Purchasing.PurchaseOrders table in the WideWorldImporters database and returns data ordered by the ExpectedDeliveryDate column ascending.

SELECT
  PurchaseOrderID, ExpectedDeliveryDate
FROM
  Purchasing.PurchaseOrders
ORDER BY
  ExpectedDeliveryDate;

By default, the rows with the ExpectedDeliveryDate set to NULL will be extracted first, it is an implementation choice of the T-SQL language which considers, in this case, NULLs less than any date.

For this query, we want to flip the ordering, we want to have the rows with ExpectedDeliveryDate different to NULL first, in ascending order, and then the rows with Expected Delivery Date set to NULL.

How can we do? A possible solution is to apply the CASE expression within the ORDER BY clause as you can see in the following query.

SELECT
  PurchaseOrderID, ExpectedDeliveryDate
FROM
  Purchasing.PurchaseOrders
ORDER BY
  CASE
    WHEN (ExpectedDeliveryDate IS NOT NULL) THEN 0 ELSE 1
  END;

The query is not sargable, it uses the IX_Purchasing_PurchaseOrders_ExpectedDeliveryDate index for the ExpectedDeliveryDate column on which an Index Scan is performed instead of an optimized Index Seek.

The following picture shows the (non-sargable) query execution plan.

How can we make this query sargable maintaining the prerequisite on sorting?

You can split the query into two queries, the first one will extract the rows with values in ExpectedDeliveryDate different to NULL, the second query will extract the rows with NULLs in ExpectedDeliveryDate. The UNION ALL clause will allow you to merge the datasets and the subsequent ORDER BY clause will apply the required ordering.

SELECT
  PurchaseOrderID, ExpectedDeliveryDate, SortOrder = 0
FROM
  Purchasing.PurchaseOrders
WHERE
  ExpectedDeliveryDate IS NOT NULL
UNION ALL
SELECT
  PurchaseOrderID, ExpectedDeliveryDate, SortOrder = 1
FROM
  Purchasing.PurchaseOrders
WHERE
  ExpectedDeliveryDate IS NULL
ORDER BY
  SortOrder, ExpectedDeliveryDate;

If we run the query we will notice that the IX_Purchasing_PurchaseOrders_ExpectedDeliveryDate index is accessed twice to read a particular range of rows (Index Seek). The first access will return rows with non-NULL values in ExpectedDeliveryDate. The second access will return the rows with the NULL value in the ExpectedDeliveryDate column. The two datasets are joined using the Merge Join operator as shown in the following picture. The query is sargable.

Now we want to implement a stored procedure that is able to extract the purchase orders with a specific delivery date and those whose delivery date is undefined (NULL). The following T-SQL piece of code implements the sp_undefined_deliverydate stored procedure in the Purchasing schema.

CREATE PROCEDURE Purchasing.sp_undefined_deliverydate
(@DeliveryDate Date)
AS BEGIN
  SELECT
    PurchaseOrderID, ExpectedDeliveryDate
  FROM
    Purchasing.PurchaseOrders
  WHERE
    ExpectedDeliveryDate = @DeliveryDate;
END;

What happens if the stored procedure is executed with the @DeliveryDate parameter set to NULL?

EXEC Purchasing.sp_undefined_deliverydate @DeliveryDate = NULL;

The comparison operator “=” used with the ANSI_NULLS option set to ON (default for each connection) will evaluate each NULL value different from any other NULL. When it evaluates the expression NULL = NULL (?) it will return false.

The returned dataset will be empty. From a performance point of view, the query is sargable but it must be modified to handle NULLs properly. Some of you may have thought of using the T-SQL ISNULL function which returns a specified value if a NULL value is encountered. The following version of the stored procedure uses the ISNULL function.

ALTER PROCEDURE Purchasing.sp_undefined_deliverydate
(@DeliveryDate Date)
AS BEGIN
  SELECT
    PurchaseOrderID, ExpectedDeliveryDate
  FROM
    Purchasing.PurchaseOrders
  WHERE
    ISNULL(ExpectedDeliveryDate, '99991231') = ISNULL(@DeliveryDate, '99991231');
END;

When the stored procedure is executed with the @DeliveryDate parameter set to NULL, the returned dataset is correct but the internal query is not sargable as shown in the following execution plan.

Can we optimize the query by making it sargable in an elegant and efficient way? The answer is Yes! We can use the T-SQL INTERSECT operator. INTERSECT returns distinct rows that are output by both the left and right input queries operator. As the name of the operator suggests, the intersection of the datasets provided in input is returned. The stored procedure is modified in this way..

ALTER PROCEDURE Purchasing.sp_undefined_deliverydate
(@DeliveryDate Date)
AS BEGIN
  SELECT
    PurchaseOrderID, ExpectedDeliveryDate
  FROM
    Purchasing.PurchaseOrders
  WHERE
    EXISTS(SELECT ExpectedDeliveryDate
           INTERSECT
           SELECT @DeliveryDate);
END;

Repeating the execution of the stored procedure..

EXEC Purchasing.sp_undefined_deliverydate @DeliveryDate = NULL;

The execution plan shown in the following picture now shows the Index Seek operator applied to the IX_Purchasing_PurchaseOrders_ExpectedDeliveryDate index, the query is sargable!

Summary

One of the steps in the query optimization process is to convert non-sargable predicates to sargable predicates. Handling of NULLs ​​always deserves to be checked, especially when using parameters passed to functions or stored procedures. The INTERSECT operator is very useful when it is necessary to intersect two datasets, even when a dataset is empty.

Enjoy!