SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS dbo.Example;
CREATE TABLE dbo.Example
(
    c1 integer NOT NULL,
    c2 integer NULL,

    INDEX CCS CLUSTERED COLUMNSTORE,
    INDEX IX NONCLUSTERED (c1)
);
INSERT dbo.Example
    (c1, c2)
VALUES
    (1, NULL);
1 rows affected

DECLARE @c2 integer = NULL;

-- Returns one row but should not
SELECT
    E.* 
FROM dbo.Example AS E 
    WITH (INDEX(IX))
WHERE
    E.c2 = @c2;


c1
c2


1
null


-- Move row into compressed row group
ALTER INDEX CCS ON dbo.Example 
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
-- Add another row to delta store
INSERT dbo.Example
    (c1, c2)
VALUES
    (2, NULL);
1 rows affected

-- One row returned (delta store) but not the other
DECLARE @c2 integer = NULL;

SELECT
    E.* 
FROM dbo.Example AS E 
    WITH (INDEX(IX))
WHERE
    E.c2 = @c2;


c1
c2


2
null