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"?