clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1716855 fiddles created (23943 in the last week).

DROP TABLE IF EXISTS dbo.Example;
 hidden batch(es)


CREATE TABLE dbo.Example ( c1 integer NOT NULL, c2 integer NULL, INDEX CCS CLUSTERED COLUMNSTORE, INDEX IX NONCLUSTERED (c1) );
 hidden batch(es)


INSERT dbo.Example (c1, c2) VALUES (1, NULL);
1 rows affected
 hidden batch(es)


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
 hidden batch(es)


-- Move row into compressed row group ALTER INDEX CCS ON dbo.Example REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
 hidden batch(es)


-- Add another row to delta store INSERT dbo.Example (c1, c2) VALUES (2, NULL);
1 rows affected
 hidden batch(es)


-- 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
 hidden batch(es)