Why does using a TSQL variable in a WHERE clause give different results than using a constant of the same value?

Question

When I run the following two SQL statements (SQL Server 2008 R2), I get different results. The first excludes strings where pernr is NULL; the second includes them.

SELECT pernr FROM tbl
WHERE pernr <> 123;

DECLARE @input int = 123; 
SELECT pernr FROM tbl
WHERE pernr <> @input;

Why is this? What's going on here? I would think that SQL Server will replace my variable with a value and would evaluate these queries the same way, but obviously not! I run them in SSMS.

Here is a reproduction of the script:

SET ANSI_NULLS OFF;

CREATE TABLE tbl (pernr INT NULL);
GO

INSERT INTO tbl (pernr)
VALUES (NULL)
    ,(123)
    ,(NULL)
    ,(456);



SELECT pernr FROM tbl
WHERE pernr <> 123;

DECLARE @input int = 123; 
SELECT pernr FROM tbl
WHERE pernr <> @input;

Results:

-------
456


-------
NULL
NULL
456

Answer 1

Oh Hai SAP

I assume you have SET ANSI_NULLS OFF;

If you have SET ANSI_NULLS ON;, you will find that they return the same

What returns select sessionproperty('ANSI_NULLS')?

, , -