SHARE
TWEET

Untitled

a guest Dec 10th, 2017 34 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --create parent table with 3 column composite primary key
  2. CREATE TABLE dbo.Parent(
  3.      Col1 int NOT NULL
  4.     ,Col2 int NOT NULL
  5.     ,Col3 int NOT NULL
  6.     ,CONSTRAINT PK_Parent PRIMARY KEY(Col1, Col2, Col3)
  7.      );
  8.  
  9. --create child table with 300 columns
  10. DECLARE @SQL nvarchar(MAX);
  11. WITH
  12.      t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
  13.     ,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
  14. SELECT @SQL = N'CREATE TABLE dbo.Child(' +
  15.     STRING_AGG(N'Col' + CAST(num AS nvarchar(MAX)) + N' int NOT NULL',N',')
  16.     + N');'
  17. FROM t1k
  18. WHERE num <= 300;
  19. EXEC sp_executesql @SQL;
  20.  
  21. --create 255 composite foreign keys of 3 columns each
  22. WITH
  23.      t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
  24.     ,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
  25. SELECT @SQL = STRING_AGG(
  26.       N'ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent_' + N'Col' + CAST(num AS nvarchar(MAX))
  27.     + N' FOREIGN KEY(Col' + CAST(num AS nvarchar(MAX)) + N', Col' + CAST(num + 1 AS nvarchar(MAX)) + N', Col' + CAST(num + 2 AS nvarchar(MAX))
  28.     + N') REFERENCES dbo.Parent(Col1, Col2, Col3);', N'')
  29. FROM t1k
  30. WHERE num <= 255;
  31. EXEC sp_executesql @SQL;
  32.  
  33. --results of Foreign Key References Check before/after each index created
  34. DELETE FROM dbo.Parent; --255 references, 255 non-matching
  35. CREATE INDEX idx1 ON dbo.Child(Col1);
  36. DELETE FROM dbo.Parent; --255 references, 255 non-matching
  37. CREATE INDEX idx2 ON dbo.Child(Col2,Col3);
  38. DELETE FROM dbo.Parent; --255 references, 255 non-matching
  39. CREATE INDEX idx3 ON dbo.Child(Col4,Col5,Col6);
  40. DELETE FROM dbo.Parent; --255 references, 254 non-matching
  41. CREATE INDEX idx4 ON dbo.Child(Col9,Col8,Col7);
  42. DELETE FROM dbo.Parent; --255 references, 253 non-matching
  43. CREATE INDEX idx5 ON dbo.Child(Col11,Col10,Col12);
  44. DELETE FROM dbo.Parent; --255 references, 252 non-matching
  45. CREATE INDEX idx6 ON dbo.Child(Col13,Col14) INCLUDE(Col15);
  46. DELETE FROM dbo.Parent; --255 references, 252 non-matching
  47. CREATE INDEX idx7 ON dbo.Child(Col16) INCLUDE(Col17,Col18);
  48. DELETE FROM dbo.Parent; --255 references, 252 non-matching
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top