Now let’s look at the example. Open two connections to SQL Server
(call them connection 1 and connection 2). Run the following code
in connection 1:
CREATE TABLE dbo.T1(col1 INT);
INSERT INTO dbo.T1 VALUES( 42);
BEGIN TRAN
INSERT INTO dbo.T1 VALUES(1759);
Then, run the following code in connection 2:
SELECT COALESCE( (SELECT SUM(col1) FROM dbo.T1), 0 );
Back in connection 1, run the following code:
DELETE FROM dbo.T1;
COMMIT TRAN
DROP TABLE dbo.T1;
Connection 2 returns a NULL. Can you explain how this could possibly happen?
In order to explain what happened, you need to examine the execution plan for the query in connection 2 shown in Figure 2. The
COALESCE expression in the query in connection 2 was internally
translated to the following CASE expression:
CASE
WHEN (SELECT SUM(col1) FROM dbo.T1) IS NOT NULL
THEN (SELECT SUM(col1) FROM dbo.T1)
ELSE 0
END
www.sqlmag.com
sql server Pro / may 2013
