Guest User

Untitled

a guest
Aug 28th, 2017
71
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS dbo.People;
  2.  
  3. CREATE TABLE dbo.People (
  4.     PersonID bigint PRIMARY KEY,
  5.     FirstName varchar(100) NOT NULL,
  6.     LastName varchar(100) NOT NULL,
  7.     SSN varchar(11)
  8.         MASKED WITH (FUNCTION = 'default()') NULL
  9. );
  10.  
  11. INSERT INTO dbo.People WITH (TABLOCK)
  12. SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  13. , REPLICATE('A', 10)
  14. , REPLICATE('Z', 12)
  15. , RIGHT('000' + CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(11)), 3)
  16.      + '-' + RIGHT('00' + CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(11)), 2)
  17.      + '-' + RIGHT('0000' + CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(11)), 4)
  18. FROM master..spt_values t1
  19. CROSS JOIN master..spt_values t2;
  20.  
  21. -- new columns
  22. ALTER TABLE People ADD SSN1 AS ISNULL(CAST(SUBSTRING(SSN, 1, 3) AS INT), 0) PERSISTED;
  23. ALTER TABLE People ADD SSN2 AS ISNULL(CAST(SUBSTRING(SSN, 5, 2) AS INT), 0) PERSISTED;
  24. ALTER TABLE People ADD SSN3 AS ISNULL(CAST(SUBSTRING(SSN, 8, 4) AS INT), 0) PERSISTED;
  25.  
  26.  
  27. SELECT TOP (10000)
  28.     ISNULL(CAST(RN AS INT), 0) NUM
  29. INTO #t_10000
  30. FROM
  31. (
  32.     SELECT -1 + ROW_NUMBER()
  33.         OVER (ORDER BY (SELECT NULL)) RN
  34.     FROM master..spt_values t1
  35.     CROSS JOIN master..spt_values t2
  36. ) t;
  37.  
  38. CREATE CLUSTERED INDEX CI ON #t_10000 (NUM);
  39.  
  40. CREATE TABLE #BATCH_MODE_PLZ (
  41.     I INT
  42.     , INDEX C CLUSTERED COLUMNSTORE
  43. );
  44.  
  45. DROP TABLE IF EXISTS #t;
  46.  
  47.  
  48. SELECT
  49.      p.PersonID
  50.        , p.FirstName
  51.        , p.LastName
  52.        , t1000.NUM
  53.         + '-' + t100.NUM
  54.         + '-' + t10000.NUM AS SSN
  55.     into #t
  56. FROM dbo.People p
  57. LEFT OUTER JOIN #t_10000 t1000
  58.     ON p.SSN1 = t1000.NUM
  59. LEFT OUTER JOIN #t_10000 t100
  60.     ON p.SSN2 = t100.NUM
  61. LEFT OUTER JOIN #t_10000 t10000
  62.     ON p.SSN3 = t10000.NUM
  63. LEFT OUTER JOIN #BATCH_MODE_PLZ ON 1 = 0
  64. OPTION (QUERYTRACEON 176);
RAW Paste Data