w2PGiXKH

1IF OBJECT_ID('dbo.T1') IS NOT NULL
2	DROP TABLE dbo.T1;
3
4CREATE TABLE dbo.T1 (ti INT, ty CHAR(1), tv VARCHAR(30));
5GO
6
7INSERT INTO dbo.T1 VALUES (1,'a','0');
8INSERT INTO dbo.T1 VALUES (2,'a','1');
9INSERT INTO dbo.T1 VALUES (3,'a','2');
10INSERT INTO dbo.T1 VALUES (4,'a','3');
11INSERT INTO dbo.T1 VALUES (1,'b','abc1');
12GO
13
14WITH T1_Filtered AS (
15SELECT * FROM dbo.T1 WHERE ty='a')
16,A AS (
17SELECT * FROM dbo.T1 a WHERE ty='a' AND NOT EXISTS ( SELECT 1 FROM T1_Filtered b WHERE b.tv = a.ti ) )
18SELECT * FROM A
19
20GO
21
22CREATE CLUSTERED COLUMNSTORE INDEX CCI_T1 ON dbo.T1 WITH (COMPRESSION_DELAY=0);
23GO
24
25WITH T1_Filtered AS (
26SELECT * FROM dbo.T1 WHERE ty='a')
27,A AS (
28SELECT * FROM dbo.T1 a WHERE ty='a' AND NOT EXISTS ( SELECT 1 FROM T1_Filtered b WHERE b.tv = a.ti ) )
29SELECT * FROM A
30
31GO
32
33
34CREATE NONCLUSTERED INDEX IX_T1 ON dbo.t1 ( ti, ty, tv );
35GO
36WITH T1_Filtered AS (
37SELECT * FROM dbo.T1 WHERE ty='a')
38,A AS (
39SELECT * FROM dbo.T1 a WHERE ty='a' AND NOT EXISTS ( SELECT 1 FROM T1_Filtered b WHERE b.tv = a.ti ) )
40SELECT * FROM A