vT9WyZia

1DROP TABLE IF EXISTS dbo.SSNS;
2
3CREATE TABLE dbo.SSNS (
4ID BIGINT NOT NULL,
5SSN VARCHAR(25) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = SSNS__SSN,  
6        ENCRYPTION_TYPE = DETERMINISTIC ,  
7        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
8);
9
10GO
11DECLARE @ssn VARCHAR(25) = '111-11-1111';
12INSERT INTO dbo.SSNS VALUES (1, @ssn);
13GO
14DECLARE @ssn VARCHAR(25) = 'SEAN GALLARDY WAS HERE';
15INSERT INTO dbo.SSNS VALUES (2, @ssn);
16GO 
17     
18DROP TABLE IF EXISTS #t_100;
19DROP TABLE IF EXISTS #t_1000;
20DROP TABLE IF EXISTS #t_10000;
21
22
23SELECT TOP (100)
24    RIGHT('0' + CAST(t.RN AS VARCHAR(10)), 2) NUM
25INTO #t_100
26FROM
27(
28    SELECT -1 + ROW_NUMBER()
29        OVER (ORDER BY (SELECT NULL)) RN
30    FROM master..spt_values t1
31    CROSS JOIN master..spt_values t2
32) t;
33
34
35-- only insert 0.1% of data as a proof of concept
36SELECT '111' NUM
37INTO #t_1000;
38
39--SELECT TOP (1000)
40--  RIGHT('00' + CAST(t.RN AS VARCHAR(10)), 3) NUM
41--INTO #t_1000
42--FROM
43--(
44--  SELECT -1 + ROW_NUMBER()
45--      OVER (ORDER BY (SELECT NULL)) RN
46--  FROM master..spt_values t1
47--  CROSS JOIN master..spt_values t2
48--) t;
49
50SELECT TOP (10000)
51    RIGHT('000' + CAST(t.RN AS VARCHAR(10)), 4) NUM
52INTO #t_10000
53FROM
54(
55    SELECT -1 + ROW_NUMBER()
56        OVER (ORDER BY (SELECT NULL)) RN
57    FROM master..spt_values t1
58    CROSS JOIN master..spt_values t2
59) t;
60
61DROP TABLE IF EXISTS dbo.ALL_VALID_SSNS_POC;
62
63CREATE TABLE dbo.ALL_VALID_SSNS_POC (
64SSN VARCHAR(25) COLLATE  Latin1_General_BIN2
65);
66
67
68-- about 5 minutes for all data, significantly faster for 1 million rows
69INSERT INTO dbo.ALL_VALID_SSNS_POC WITH (TABLOCK)
70SELECT
71     t1000.NUM
72        + '-' + t100.NUM
73        + '-' + t10000.NUM AS SSN
74FROM (SELECT TOP (1000) * FROM #t_1000) t1000
75CROSS JOIN #t_100 t100
76CROSS JOIN #t_10000 t10000
77OPTION (MAXDOP 8, NO_PERFORMANCE_SPOOL, FORCE ORDER);
78
79CREATE CLUSTERED INDEX CI ON dbo.ALL_VALID_SSNS_POC (SSN); 
80     
81UPDATE [dbo].[ALL_VALID_SSNS] SET   
82[dbo].[ALL_VALID_SSNS].[tceGuidCol1] = NEWID() 
83     
84SELECT s.*
85FROM dbo.SSNS s
86WHERE EXISTS (
87    SELECT 1
88    FROM dbo.ALL_VALID_SSNS_POC vs
89    WHERE s.SSN = vs.SSN
90); 
91     
92SELECT s.*
93FROM dbo.SSNS s
94WHERE NOT EXISTS (
95    SELECT 1
96    FROM dbo.ALL_VALID_SSNS_POC vs
97    WHERE s.SSN = vs.SSN
98);