A Little Help Please

SQL Server can optimize the logic of your queries resulting in a better execution plan which you may not have considered. I wanted to prove that the AND operation in TSQL is the equivalent of the && operation found in C#.

When you compare two Boolean values to determine if both are true you combine the two values using &&. If (testValue1 && testValue2) first tests if testVlaue1 is true. If not, it does no further work. If testValue1 is true, then it evaluates testValue2. If both are true, the test is considered true, and the true branch of the logic is executed. This is really helpful when working with nullable types. In the following example I have a nullable value. I first test for it being null, and then for the value contents. If I don’t test for null first, the application has a null value exception. If the second part of the if statement is always evaluated then once again I get a null value exception. Since && short circuits execution when the first test fails, I don’t have to test for null and a value in different if statements.

var int? myNullableInt = null;
if(myNullableInt!=null && myNullableInt ==23) DoSomeWork();

SQL Server seems to be even more intelligent. I tried to do a similar test in SQL Server 2014. I was pleased with what I found. I started with the code as you see in example 2.

ICREATE TABLE #Test (Id int null)
GO

INSERT INTO #Test VALUES
 (NULL)
,(1)
,(10)
GO

SELECT *
FROM #Test
WHERE   Id IS  NOT NULL
AND     ID = 1

After executing this code I reviewed the query plan, and was pleased to find that it only evaluates the condition WHERE Id = 1. It doesn’t even evaluate the IS NOT NULL condition, because it is intelligent enough to know that if Id is ever 1, it will never be NULL. Here’s a screen capture of the query plan.

I thought that was really cool. So, I wanted to make the problem a little harder. I added two other conditions to my where clause to see what would happen.

WHERE 1=1
AND Id IS NOT NULL
AND Id != 2
AND Id =1

Once again, to my great pleasure, the query plan only evaluates Id = 1. It was exacly like the previous test. How cool is that.

I’m sure that behind the scenes it is determining that the value for one record has null in the Id field. It has to in order to compare the value to 1. But, I am unable to break into the inner workings to validate that assumption.

I wouldn’t count on the query optimizer to solve all my coding problems. But, as this example shows, there is quite a bit of bang for the buck.

Cheers,

Ben