Execution of ISNULL in SQL Server

Question

Here is how i am using ISNULL condition to check for student address. It works fine but how ISNULL function treat the null codition i.e the second parameter which is display if first condition is null.

Will it calculate Value for second parameter when first condition is not null?

select 
   ...
   ...
   (CASE 
      WHEN st.ADDRESS='Y' THEN st.LOCATION
        ELSE 
          ISNULL(
                 (SELECT TOP 1 STDLOC.LOCATION FROM STDLOC 
                  INNER JOIN COMLOC ON STKLOC.LOCATION=COMLOC.CODE  AND COMLOC.ADDRESS='Y' 
                  WHERE STDLOC.ZIBCODE=st.ZIBCODE)
                ,(SELECT TOP 1 COMLOC.LOCATION  FROM COMLOC COMLOC.ZIBCODE=st.ZIBCODE))                       
       END
        ) AS STDUDENTLOCATION
   FROM STUDENT st

Answer 1

Both queries inside the ISNULL will be executed, even if the first query will return a value.

Here is a simple test I've made:

Create and populate sample table:

DECLARE @T AS TABLE
(
    Col int
)
INSERT INTO @T Values(1),(2)

SELECT ISNULL(
    (SELECT TOP 1 Col FROM @T ORDER BY Col DESC), 
    (SELECT TOP 1 Col FROM @T ORDER BY Col )
)

Execution plan image:

As you can clearly see, the execution plan includes both queries.

Answer 2

ISNULL is a T-SQL specific function that will use the specified second parameter as the return value if the first parameter is NULL(https://msdn.microsoft.com/en-us/library/ms184325.aspx).

Use COALESCE function if you want to return the first non-null value from multiple arguments, and this is a standard function that is supported by all types of relational databases.

This POST provide a good answer for the question:

Is Sql Server's ISNULL() function lazy/short-circuited?

Answer 3

I also was looking for an answer. After some reading I came out with my own way to check it.

Deviding by zero will give an error, so we can try:

SELECT ISNULL( (SELECT TOP 1 object_id FROM sys.columns), 5 / 0)

This will give correct result. BUT

SELECT ISNULL( (SELECT TOP 0 object_id FROM sys.columns), 5 / 0)

It will throw an error, because result of first query gives NULL so it tries the second query which fails