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
PubSvrPubDBRepNameSubSvrSubDBLvl
Svr1DB1Rep_DB1Svr2DB10
Svr1DB2Rep_DB2Svr2DB20
Svr1DB2Rep_DB2Svr3DB20
Svr2DB1Rep_DB1_aSvr3DB11
Svr2DB1Rep_DB1_aSvr4DB11
Svr3DB2Rep_DB2_bSvr4DB22
*/
WITH DBs (PubSvr, PubDB, RepName, SubSvr, SubDB, Lvl) AS
(
SELECTPubSvr, PubDB, RepName, SubSvr, SubDB, 0 AS Lvl
FROM#RepData
GROUP BY PubSvr, PubDB, RepName, SubSvr, SubDB
UNION ALL
SELECTR.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
)
SELECTPubSvr, PubDB, RepName, SubSvr, SubDB, Lvl
FROMDBs
ORDER BY Lvl, RepName
OPTION (MAXRECURSION 2)
/* Actual Results
PubSvrPubDBRepNameSubSvrSubDBLvl
Svr1DB1Rep_DB1Svr2DB10
Svr2DB1Rep_DB1_aSvr3DB10
Svr2DB1Rep_DB1_aSvr4DB10
Svr3DB1Rep_DB1_bSvr4DB10
Svr1DB2Rep_DB2Svr2DB20
Svr1DB2Rep_DB2Svr3DB20
Svr1DB1Rep_DB1Svr2DB11
Svr1DB1Rep_DB1Svr2DB11
Svr2DB1Rep_DB1_aSvr3DB11
Svr1DB1Rep_DB1Svr2DB12
*/
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
(
SELECTPubSvr, PubDB, RepName, SubSvr, SubDB, 0 AS Lvl
FROM#RepData
WHERE PubSvr = 'Svr1'
UNION ALL
SELECTR.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
)
SELECTPubSvr, PubDB, RepName, SubSvr, SubDB, Lvl
FROMDBs
ORDER BY Lvl, RepName
OPTION (MAXRECURSION 2)
/* results
PubSvrPubDBRepNameSubSvrSubDBLvl
Svr1DB1Rep_DB1Svr2DB10
Svr1DB2Rep_DB2Svr2DB20
Svr1DB2Rep_DB2Svr3DB20
Svr2DB1Rep_DB1_aSvr3DB11
Svr2DB1Rep_DB1_aSvr4DB11
Svr3DB2Rep_DB2_bSvr4DB21
*/
DROP TABLE #RepData
November 13, 2012 at 3:29 am
Here's an article for the recursively challenged exploring rCTEs by example:
http://www.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
(
SELECTPubSvr, PubDB, RepName, SubSvr, SubDB, 0 AS Lvl
FROM#RepData
WHERE IsParent = 1
UNION ALL
SELECTR.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
)
SELECTPubSvr, PubDB, RepName, SubSvr, SubDB, Lvl
FROMDBs
ORDER BY Lvl, RepName
OPTION (MAXRECURSION 2)
/* results
PubSvrPubDBRepNameSubSvrSubDBLvl
Svr1DB1Rep_DB1Svr2DB10
Svr1DB2Rep_DB2Svr2DB20
Svr1DB2Rep_DB2Svr3DB20
Svr2DB1Rep_DB1_aSvr3DB11
Svr2DB1Rep_DB1_aSvr4DB11
Svr3DB2Rep_DB2_bSvr4DB21
*/
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