about CTE in SQL 2k5

Hi all,
I tested a new feature of SQL2005 (named cte for recursive selection)
and found the order of the result was weird.
According to the description of help:
1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base
result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an
output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.
But the result was just like the stack-calls.
Is there something wrong on the description of the help?
table creation script:
CREATE TABLE tbUser(
userId int NOT NULL,
mgrId int NULL,
CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table values:
insert tbUser values(1,null)
insert tbUser values(2,1)
insert tbUser values(3,2)
insert tbUser values(4,2)
CTE scripts
WITH reps_cte (userId, mgrId, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
higher level of the mgr
FROM reps_cte inner join tbUser -- Join with Employee
on reps_cte.mgrId= tbUser.userId -- This employee's manager
where recursion_level<=20 -- up to 20 levels of mgmt
)
Result:
1 NULL 0
2 1 0
3 2 0
4 2 0
4 1 1
4 NULL 2
3 1 1
3 NULL 2
2 NULL 1
Expecting result:
1 NULL 0 - T0 result
2 1 0 - T0 result
3 2 0 - T0 result
4 2 0 - T0 result
2 NULL 1 - T1 result
3 1 1 - T1 result
4 1 1 - T1 result
3 NULL 2 - T2 result
4 NULL 2 - T2 result
Regards,
congling"congling" <congling@.hotmail.com> wrote in message
news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
The order of a result is always undefined unless the query contains an ORDER
BY clause. The BOL has this description under the heading of "Pseudocode
and Semantics", and it's is a logical description of the result set, not a
guarantee about how it is processed or the order in which the rows are
returned.
David|||congling wrote:
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
> table creation script:
> CREATE TABLE tbUser(
> userId int NOT NULL,
> mgrId int NULL,
> CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
> (
> [userId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> table values:
> insert tbUser values(1,null)
> insert tbUser values(2,1)
> insert tbUser values(3,2)
> insert tbUser values(4,2)
> CTE scripts
> WITH reps_cte (userId, mgrId, recursion_level)
> AS
> (
> SELECT userId, mgrId, 0 FROM tbUser
> UNION ALL
> SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
> higher level of the mgr
> FROM reps_cte inner join tbUser -- Join with Employe
e
> on reps_cte.mgrId= tbUser.userId -- This employee's manag
er
> where recursion_level<=20 -- up to 20 levels of mgmt
> )
> Result:
> 1 NULL 0
> 2 1 0
> 3 2 0
> 4 2 0
> 4 1 1
> 4 NULL 2
> 3 1 1
> 3 NULL 2
> 2 NULL 1
> Expecting result:
> 1 NULL 0 - T0 result
> 2 1 0 - T0 result
> 3 2 0 - T0 result
> 4 2 0 - T0 result
> 2 NULL 1 - T1 result
> 3 1 1 - T1 result
> 4 1 1 - T1 result
> 3 NULL 2 - T2 result
> 4 NULL 2 - T2 result
>
> Regards,
> congling
If you don't specify ORDER BY then the sorting of the result is
undefined. Try:
WITH reps_cte (userid, mgrid, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1
FROM reps_cte inner join tbUser
on reps_cte.mgrId= tbUser.userId
where recursion_level<=1
)
SELECT userid, mgrid, recursion_level
FROM reps_cte
ORDER BY recursion_level, mgrid, userid ;
David Portas
SQL Server MVP
--|||thx
Regards,
congling
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> д?:%23CcF$FxAGHA.20
40@.TK2MSFTNGP14.phx.gbl...
> "congling" <congling@.hotmail.com> wrote in message
> news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> The order of a result is always undefined unless the query contains an
> ORDER BY clause. The BOL has this description under the heading of
> "Pseudocode and Semantics", and it's is a logical description of the
> result set, not a guarantee about how it is processed or the order in
> which the rows are returned.
> David
>