SUM with NULL Values in SQL Server

Follow on the SUM with NULL Values in Teradata post, I wanted to check the same thing in SQL Server.

When you SUM a value that is nullable, does it work correctly if there are null values? Here's the test and the result. The answer is yes, it works fine here too; it just requires some change in syntax.

Note that if you set up your flag column as a bit column, you have to add a CONVERT in the SUM since you can't add bit columns.

It is also interesting to note that SQL Server throws the following warning after the results of the first query: "Warning: Null value is eliminated by an aggregate or other SET operation."

DECLARE @rgWork1 TABLE (
    client      INT         NOT NULL,
    flag        bit 
);

INSERT INTO @rgWork1 (client, flag) VALUES (1, 1); 
INSERT INTO @rgWork1 (client, flag) VALUES (2, 0);
INSERT INTO @rgWork1 (client, flag) VALUES (3, NULL);

SELECT SUM(CONVERT(INT, flag)) AS FlagSum 
FROM @rgWork1;

SELECT SUM(CONVERT(INT, flag)) AS FlagSum 
FROM @rgWork1
WHERE flag = 1;

SELECT SUM(CONVERT(INT, flag)) AS FlagSum
FROM @rgWork1 
WHERE flag IS NOT NULL;

SELECT SUM(CONVERT(INT, flag)) AS FlagSum 
FROM @rgWork1
WHERE (flag = 1 OR flag IS NOT NULL);

All of these queries return the correct answer of 1.