p0PeuVss

1DROP TABLE IF EXISTS #HASH_INDEX_DEMO;
2
3CREATE TABLE #HASH_INDEX_DEMO (
4    ID BIGINT NOT NULL,
5    BIG_COLUMN_FOR_U VARCHAR(8000) NOT NULL,
6    SMALL_COLUMN VARCHAR(10) NOT NULL
7);
8
9INSERT INTO #HASH_INDEX_DEMO WITH (TABLOCK)
10SELECT RN, REPLICATE(CHAR(65 + RN % 26), (RN % 43) * (RN % 119)), 'SMALL'
11FROM 
12(
13    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
14    FROM master..spt_values t1
15    CROSS JOIN master..spt_values t2
16) q; 
17     
18CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U); 
19     
20SELECT ID, SMALL_COLUMN
21FROM #HASH_INDEX_DEMO
22WHERE BIG_COLUMN_FOR_U = 'A'
23OPTION (MAXDOP 1); 
24     
25ALTER TABLE #HASH_INDEX_DEMO ADD BIG_COLUMN_FOR_U_CHECKSUM AS CHECKSUM(BIG_COLUMN_FOR_U);
26
27CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U_CHECKSUM); 
28     
29SELECT ID, SMALL_COLUMN
30FROM #HASH_INDEX_DEMO
31WHERE BIG_COLUMN_FOR_U_CHECKSUM = CHECKSUM('A') AND BIG_COLUMN_FOR_U = 'A'
32OPTION (MAXDOP 1);