Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --create parent table with 3 column composite primary key
- CREATE TABLE dbo.Parent(
- Col1 int NOT NULL
- ,Col2 int NOT NULL
- ,Col3 int NOT NULL
- ,CONSTRAINT PK_Parent PRIMARY KEY(Col1, Col2, Col3)
- );
- --create child table with 300 columns
- DECLARE @SQL nvarchar(MAX);
- WITH
- t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
- ,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)
- SELECT @SQL = N'CREATE TABLE dbo.Child(' +
- STRING_AGG(N'Col' + CAST(num AS nvarchar(MAX)) + N' int NOT NULL',N',')
- + N');'
- FROM t1k
- WHERE num <= 300;
- EXEC sp_executesql @SQL;
- --create 255 composite foreign keys of 3 columns each
- WITH
- t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
- ,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)
- SELECT @SQL = STRING_AGG(
- N'ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent_' + N'Col' + CAST(num AS nvarchar(MAX))
- + 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))
- + N') REFERENCES dbo.Parent(Col1, Col2, Col3);', N'')
- FROM t1k
- WHERE num <= 255;
- EXEC sp_executesql @SQL;
- --results of Foreign Key References Check before/after each index created
- DELETE FROM dbo.Parent; --255 references, 255 non-matching
- CREATE INDEX idx1 ON dbo.Child(Col1);
- DELETE FROM dbo.Parent; --255 references, 255 non-matching
- CREATE INDEX idx2 ON dbo.Child(Col2,Col3);
- DELETE FROM dbo.Parent; --255 references, 255 non-matching
- CREATE INDEX idx3 ON dbo.Child(Col4,Col5,Col6);
- DELETE FROM dbo.Parent; --255 references, 254 non-matching
- CREATE INDEX idx4 ON dbo.Child(Col9,Col8,Col7);
- DELETE FROM dbo.Parent; --255 references, 253 non-matching
- CREATE INDEX idx5 ON dbo.Child(Col11,Col10,Col12);
- DELETE FROM dbo.Parent; --255 references, 252 non-matching
- CREATE INDEX idx6 ON dbo.Child(Col13,Col14) INCLUDE(Col15);
- DELETE FROM dbo.Parent; --255 references, 252 non-matching
- CREATE INDEX idx7 ON dbo.Child(Col16) INCLUDE(Col17,Col18);
- DELETE FROM dbo.Parent; --255 references, 252 non-matching
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement