3

It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given.

If that statement is true, what is a better way to write the following?

SELECT * FROM dbo.DpdRoute 
    WHERE DestinationCountry =@COUNTRY 
    AND (  (BeginPostCode IS NULL      AND ENDPOSTCODE IS NULL) 
        OR (BeginPostCode  = @POSTCODE AND ENDPOSTCODE IS NULL)
        OR (BeginPostCode IS NULL      AND ENDPOSTCODE  = @POSTCODE)
        OR (BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE))

I'm looking for a list of routes, for a given country where a postcode is either within a range of postcodes, equal to the min or max post code, or the min or max post codes are both null.

How does one "not compare to null" when the comparison is specifically about "nulls"?

  • 1
  • 2
    It's not about NULLs, it's about ORs. – Ivan Starostin Dec 12 '16 at 10:21
  • 2
    Dynamic Search Conditions in T‑SQL by Erland Sommarskog is a very good comprehensive article on this topic. In essence, in SQL Server 2008+ this type of query usually should have OPTION(RECOMPILE) hint to generate a good plan for all possible values of parameters. – Vladimir Baranov Dec 12 '16 at 11:07
  • 2
    This isn't dynamic search. There is no conditional logic checking if the variable is null and if so doing something different. It is checking if the column is null. – Martin Smith Feb 14 '17 at 19:46
  • Can the query return more than 1 rows or is it guaranteed to return max 1 row always? – ypercubeᵀᴹ Feb 14 '17 at 20:32
4

If you have an index on DestinationCountry,BeginPostCode,ENDPOSTCODE then your original query seeks into the DestinationCountry part but no further.

You may find better performance rewriting as an UNION ALL if you have that index.

The below can seek into non overlapping parts of this index.

  • An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) = (@COUNTRY,NULL, NULL)
  • An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) = (@COUNTRY,NULL, @POSTCODE)
  • An equality seek on (DestinationCountry) = (@COUNTRY) and then a range seek on the secondary key BeginPostCode <= @POSTCODE with a residual predicate on all rows that match.

__

WITH Country
     AS (SELECT *
         FROM   dbo.DpdRoute
         WHERE  DestinationCountry = @COUNTRY) 
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE IS NULL
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE = @POSTCODE
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode <= @POSTCODE
       AND ( ENDPOSTCODE >= @POSTCODE
              OR ( BeginPostCode = @POSTCODE
                   AND ENDPOSTCODE IS NULL ) ); 

enter image description here

  • Don't have time for an answer, and even less for performance tests/analysis, but do you think this could have any good potential: SELECT ... WHERE EXISTS (SELECT BeginPostCode, ENDPOSTCODE INTERSECT (SELECT * FROM (VALUES (NULL, NULL), (NULL, @PostCode), (@PostCode, NULL), (@PostCode, @PostCode)) AS v (BPC, EPC)) UNION ALL SELECT ... WHERE BeginPostCode < @PostCode AND ENDPOSTCODE > @PostCode? – Andriy M Feb 14 '17 at 21:01
  • @AndriyM - I tried this already with disappointing results i.stack.imgur.com/iNnma.png (only seeks into the country and residual predicate on the rest) – Martin Smith Feb 14 '17 at 21:03
  • @AndriyM - you mean this? The selected seek in the image again only seeks on the first key i.stack.imgur.com/NCh35.png and then has a residual predicate against all rows matching a country – Martin Smith Feb 14 '17 at 21:08
  • Yes, that one, thanks! Two seeks instead of three, not too much of a gain, I guess. Unless they have a lot of posts. – Andriy M Feb 14 '17 at 21:12
  • 1
    @MaxVernon - thanks. Hopefully you'll be able to reuse your answer on a dynamic search one soon! – Martin Smith Feb 14 '17 at 21:31
0
(    (BeginPostCode IS NULL      AND ENDPOSTCODE IS NULL) 
  OR (BeginPostCode  = @POSTCODE AND ENDPOSTCODE IS NULL)
  OR (BeginPostCode IS NULL      AND ENDPOSTCODE  = @POSTCODE)
  OR (BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE) 
)

That might be the same as

    isnull(BeginPostCode, @POSTCODE) <= @POSTCODE 
and isnull(ENDPOSTCODE,   @POSTCODE) >= @POSTCODE 

I could be wrong. I think every condition that need to pass does pass.
Not sure if something that should fail will get through.

It fails on BeginPostCode IS NULL and ENDPOSTCODE >= @POSTCODE

This might work

    (     isnull(BeginPostCode, @POSTCODE) = @POSTCODE 
      and isnull(ENDPOSTCODE,   @POSTCODE) = @POSTCODE ) 
 OR ( BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.