Last week I provided a T-SQL Challenge involving writing a recursive query that returns tables related to an input table directly or indirectly through foreign key relationships. You can find the challenge details here. The tricky part wasn’t really to come up with any solution that works, rather to come up with a solution using a recursive query. With this constraint the challenge became much more difficult—especially coming up with a solution that performs reasonably.
I’d like to thank all those who sent solutions, including Peter Larsson (Peso), Geri Reshef and Rubén Garrigós. Special thanks go to Peso for his help in benchmarking solutions in his environment and in sending corrections to other solutions. I know that many others tried to solve the puzzle and would like to thank you for your efforts—it is a difficult challenge!
The sample data I provided last week is far too simple to be used to test the performance of the solutions. Peso tested some of the solutions in his data warehouse with 160 tables and this way we got more realistic measures. I created a new set of tables—smaller than Peso’s environment, yet more complex than last week’s—and this was sufficient to show the performance differences between the solutions. Here’s the new sample data:
USE testfk;
CREATE TABLE dbo.T2(c2 INT PRIMARY KEY);
CREATE TABLE dbo.T3(c3 INT PRIMARY KEY);
CREATE TABLE dbo.T4(c4 INT PRIMARY KEY);
CREATE TABLE dbo.T5(c5 INT PRIMARY KEY);
CREATE TABLE dbo.T1(c1 INT PRIMARY KEY,
c2 INT REFERENCES dbo.T2,
c3 INT REFERENCES dbo.T3,
c4 INT REFERENCES dbo.T4,
c5 INT REFERENCES dbo.T5);
CREATE TABLE dbo.T6(c6 INT PRIMARY KEY,
c2 INT REFERENCES dbo.T2,
c3 INT REFERENCES dbo.T3);
CREATE TABLE dbo.T7(c7 INT PRIMARY KEY,
c3 INT REFERENCES dbo.T3,
c4 INT REFERENCES dbo.T4);
CREATE TABLE dbo.T8(c8 INT PRIMARY KEY,
c4 INT REFERENCES dbo.T4,
c5 INT REFERENCES dbo.T5);
CREATE TABLE dbo.T9(c9 INT PRIMARY KEY,
c2 INT REFERENCES dbo.T2,
c5 INT REFERENCES dbo.T5);
CREATE TABLE dbo.T10(c10 INT PRIMARY KEY,
c6 INT REFERENCES dbo.T6,
c3 INT REFERENCES dbo.T3);
CREATE TABLE dbo.T11(c11 INT PRIMARY KEY,
c3 INT REFERENCES dbo.T3,
c7 INT REFERENCES dbo.T7);
CREATE TABLE dbo.T12(c12 INT PRIMARY KEY,
c7 INT REFERENCES dbo.T7,
c4 INT REFERENCES dbo.T4);
CREATE TABLE dbo.T13(c13 INT PRIMARY