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 |