jigsm_shah (10/11/2011)

---

As the correct output is Null,'Is Zero', 'Is One', 'Is Two'.

I want to know why NULL is also the part of the output.

The explanation says that:

The statement CASE COL1 WHEN NULL THEN 'Is Null' doesn't evaluate to TRUTH value as NULL represents MISSING value.
Hence it returns NULL.

As the statement CASE COL1 WHEN NULL THEN 'Is Null' is not evaluated to TRUTH,then in that case it should not return any values as there is no else condition mentioned in the statement.

Kindly help me to understand this.

L' Eomot Inversé (10/10/2011)

---

Good question.

But the real answer of course is "it depends" on what the setting of ansi_nulls is; it's on by default in an SSMS query window, so the given answer is correct there; it is incorrect in a context where ansi_nulls is off, which is the default for connections where the application or the connection provider doesn't explicitly it in the connection dialog (SMSS does, SQL 2000 QA did, but some things don't). Fortunately the ansi_nulls setting is an issue which will go away - the ability to change this setting it is a deprecated feature, there are important features which don't work if it is off (indexed views, indexes on computed columns, query notifications), and there will be a new version of SQL Server in which ansi_nulls is effectively always on and can't be switched off.