Questions about handling NULLs and empty strings in nvarchar and numeric fields
Q1

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
-- returns 80, but I expected 100.


Why would you expect 100 rows? You have 20 rows where the column is NULL. Your expression evaluates to:

SELECT SUM(CASE WHEN COLUMN1 &lt;&gt; '' THEN 1 ELSE 0 END)


Since NULL means unknown, an equality or inequality comparison will yield unknown (and in this case false or, more pedantically accurate, not true). When column1 is null, SQL Server can't tell you if it is equal to 'foo' or not equal to 'foo'.

Q2

The error is due to implicit conversion and the order of the expressions. In the first query, you are comparing to a string first, then to NULL. The NULL becomes a string, because it was referenced later, and so the underlying column (as you should see in the execution plan) was implicitly converted to a string. In the second query, you are comparing to NULL first, therefore to determine the data type of the expression, it must go check the table. The table contains a numeric, so the first argument is the same as CONVERT(NUMERIC(18,2), NULL), and then it tries to convert the empty string to numeric. Try this to see why it doesn't work:

SELECT CONVERT(DECIMAL(10,2), '');


Q3

In order to use the same expression on all data types, you must be able to convert them all to the same data type. So let's say I have a table:

CREATE TABLE #foo(a VARCHAR(30), b NUMERIC(18,2));

INSERT #foo SELECT '1', NULL;
INSERT #foo SELECT NULL, 4.5;
INSERT #foo SELECT '', 5.5;


Now compare the results of these four expressions:

SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') &lt;&gt; '';
SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') = '';

SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') &lt;&gt; '';
SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') = '';