November 13, 2012 at 3:08 am
Hi,
I'm trying to get the hang of recursive CTEs.
In this test I am wanting to set a level for downstream replications, but am not getting the expected results.
What am I doing wrong here?
Resource : http://msdn.microsoft.com/en-us/library/ms175972%28v=sql.105%29.aspx
CREATE TABLE #RepData
(
PubSvr Varchar(50),
PubDB Varchar(50),
RepName Varchar(50),
SubSvr Varchar(50),
SubDB Varchar(50)
)
INSERT INTO #RepData
VALUES ('Svr1', 'DB1', 'Rep_DB1', 'Svr2', 'DB1'),
('Svr1', 'DB2', 'Rep_DB2', 'Svr2', 'DB2'),
('Svr1', 'DB2', 'Rep_DB2', 'Svr3', 'DB2'),
('Svr2', 'DB1', 'Rep_DB1_a', 'Svr3', 'DB1'),
('Svr2', 'DB1', 'Rep_DB1_a', 'Svr4', 'DB1'),
('Svr3', 'DB2', 'Rep_DB2_b', 'Svr4', 'DB2')
/* Expected results
PubSvr PubDB RepName SubSvr SubDB Lvl
Svr1 DB1 Rep_DB1 Svr2 DB1 0
Svr1 DB2 Rep_DB2 Svr2 DB2 0
Svr1 DB2 Rep_DB2 Svr3 DB2 0
Svr2 DB1 Rep_DB1_a Svr3 DB1 1
Svr2 DB1 Rep_DB1_a Svr4 DB1 1
Svr3 DB2 Rep_DB2_b Svr4 DB2 2
*/
WITH DBs (PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl) AS
(
SELECT PubSvr, PubDB, RepName, SubSvr, SubDB, 0 AS Lvl
FROM #RepData
GROUP BY PubSvr, PubDB, RepName, SubSvr, SubDB
UNION ALL
SELECT R.PubSvr, R.PubDB, R.RepName, R.SubSvr, R.SubDB, Lvl+1
FROM #RepData R
JOIN DBs D
ON D.PubSvr = R.SubSvr
AND D.PubDB = R.SubDB
)
SELECT PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl
FROM DBs
ORDER BY Lvl, RepName
OPTION (MAXRECURSION 2)
/* Actual Results
PubSvr PubDB RepName SubSvr SubDB Lvl
Svr1 DB1 Rep_DB1 Svr2 DB1 0
Svr2 DB1 Rep_DB1_a Svr3 DB1 0
Svr2 DB1 Rep_DB1_a Svr4 DB1 0
Svr3 DB1 Rep_DB1_b Svr4 DB1 0
Svr1 DB2 Rep_DB2 Svr2 DB2 0
Svr1 DB2 Rep_DB2 Svr3 DB2 0
Svr1 DB1 Rep_DB1 Svr2 DB1 1
Svr1 DB1 Rep_DB1 Svr2 DB1 1
Svr2 DB1 Rep_DB1_a Svr3 DB1 1
Svr1 DB1 Rep_DB1 Svr2 DB1 2
*/
DROP TABLE #RepData
Cheers
November 13, 2012 at 3:18 am
First off your selecting the whole of the table as your base point, so there should be a where clause in the CTE before the union WHERE PubSrv = 'Svr1'.
Then there is an incorrect join as you want to connect the base points subsrv to the ctes pubsrv.
I dont get a second level though and looking at the data there is only levels 0 and 1, unsure on the level 2
CREATE TABLE #RepData
(
PubSvr Varchar(50),
PubDB Varchar(50),
RepName Varchar(50),
SubSvr Varchar(50),
SubDB Varchar(50)
)
INSERT INTO #RepData
VALUES ('Svr1', 'DB1', 'Rep_DB1', 'Svr2', 'DB1'),
('Svr1', 'DB2', 'Rep_DB2', 'Svr2', 'DB2'),
('Svr1', 'DB2', 'Rep_DB2', 'Svr3', 'DB2'),
('Svr2', 'DB1', 'Rep_DB1_a', 'Svr3', 'DB1'),
('Svr2', 'DB1', 'Rep_DB1_a', 'Svr4', 'DB1'),
('Svr3', 'DB2', 'Rep_DB2_b', 'Svr4', 'DB2')
;WITH DBs (PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl) AS
(
SELECT PubSvr, PubDB, RepName, SubSvr, SubDB, 0 AS Lvl
FROM #RepData
WHERE PubSvr = 'Svr1'
UNION ALL
SELECT R.PubSvr, R.PubDB, R.RepName, R.SubSvr, R.SubDB, Lvl+1
FROM #RepData R
JOIN DBs D
ON D.SubSvr = R.PubSvr
AND D.SubDB = R.SubDB
)
SELECT PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl
FROM DBs
ORDER BY Lvl, RepName
OPTION (MAXRECURSION 2)
/* results
PubSvr PubDB RepName SubSvr SubDB Lvl
Svr1 DB1 Rep_DB1 Svr2 DB1 0
Svr1 DB2 Rep_DB2 Svr2 DB2 0
Svr1 DB2 Rep_DB2 Svr3 DB2 0
Svr2 DB1 Rep_DB1_a Svr3 DB1 1
Svr2 DB1 Rep_DB1_a Svr4 DB1 1
Svr3 DB2 Rep_DB2_b Svr4 DB2 1
*/
DROP TABLE #RepData
November 13, 2012 at 3:29 am
Here's an article for the recursively challenged exploring rCTEs by example:
http://qa.sqlservercentral.com/articles/T-SQL/90955/
Hope it is helpful.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 13, 2012 at 5:15 am
Thanks for your replies!
I nearly heard the click of comprehension.
@anthony.green
Indeed no level 2 with that data.
Adding in:
('Svr4', 'DB1', 'Rep_DB1_b', 'Svr5', 'DB1'),
('Svr4', 'DB3', 'Rep_DB3', 'Svr5', 'DB3')
Does give a level 2.
But the last row does not show up. It should be a level 0.
I guess when working with an hierarchy you must always have a starting point (Where PubSvr = 'Svr1'). Or is it possible to get that last row in there as well?
I would use this query to find out where I must make schema changes to a specific table. Hence the need to find all level 0.
Let me see if I can word the join correction correctly.
D.SubSvr is what I'm wanting to loop through and compare to R.PubSvr which is the Anchor or starting point.
Right?
@dwain.c
Nice read, but I not even "Recursively challenged" yet. Working my way to that level at the moment. 😛
But really impressive and mind boggling.
November 13, 2012 at 5:29 am
You need to define what consitutes your base anchor and ensure that it has a full hierarchy tree underneath it, now Svr1 doesnt have a hierarchy link to Srv4 so it doesnt return the data.
I have added an extra column to the data set which defines if the row is a parent or child and then based the base anchor off that point instead of the PubSvr column, which brings back your expected result set.
CREATE TABLE #RepData
(
PubSvr Varchar(50),
PubDB Varchar(50),
RepName Varchar(50),
SubSvr Varchar(50),
SubDB Varchar(50),
IsParent BIT
)
INSERT INTO #RepData
VALUES ('Svr1', 'DB1', 'Rep_DB1', 'Svr2', 'DB1',1),
('Svr1', 'DB2', 'Rep_DB2', 'Svr2', 'DB2',1),
('Svr1', 'DB2', 'Rep_DB2', 'Svr3', 'DB2',1),
('Svr2', 'DB1', 'Rep_DB1_a', 'Svr3', 'DB1',0),
('Svr2', 'DB1', 'Rep_DB1_a', 'Svr4', 'DB1',0),
('Svr3', 'DB2', 'Rep_DB2_b', 'Svr4', 'DB2',0),
('Svr4', 'DB1', 'Rep_DB1_b', 'Svr5', 'DB1',0),
('Svr4', 'DB3', 'Rep_DB3', 'Svr5', 'DB3',1)
;WITH DBs (PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl) AS
(
SELECT PubSvr, PubDB, RepName, SubSvr, SubDB, 0 AS Lvl
FROM #RepData
WHERE IsParent = 1
UNION ALL
SELECT R.PubSvr, R.PubDB, R.RepName, R.SubSvr, R.SubDB, Lvl+1
FROM #RepData R
JOIN DBs D
ON D.SubSvr = R.PubSvr
AND D.SubDB = R.SubDB
)
SELECT PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl
FROM DBs
ORDER BY Lvl, RepName
OPTION (MAXRECURSION 2)
/* results
PubSvr PubDB RepName SubSvr SubDB Lvl
Svr1 DB1 Rep_DB1 Svr2 DB1 0
Svr1 DB2 Rep_DB2 Svr2 DB2 0
Svr1 DB2 Rep_DB2 Svr3 DB2 0
Svr2 DB1 Rep_DB1_a Svr3 DB1 1
Svr2 DB1 Rep_DB1_a Svr4 DB1 1
Svr3 DB2 Rep_DB2_b Svr4 DB2 1
*/
DROP TABLE #RepData
November 13, 2012 at 5:52 am
Ok thanks anthony.green, much clearer now!
The point of the query would be to find the parents / grandparents, but your inclusion of the new column helped my understanding a lot.
Cheers m8!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply