Advertisement
Guest User

Untitled

a guest
Dec 10th, 2017
512
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.21 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement