Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Interesting CTE behavior.

    I had been under the assumption that a CTE referenced several times was executed once, and that result set was used each time it was referenced in the rest of the query.

    I mean, that makes sense doesn't it? It's certainly more efficient. But apparently that is not how it works, as I found out when I was debugging a CTE that select TOP 1 without a deterministic order. This recreates this issue:

    Code:
    ;with RandomSequences as
    		(select	number,
    				NEWID() as RandValue,
    				ROW_NUMBER() over (order by NEWID()) as SortOrder
    		from	master.dbo.spt_values
    		where	name is null)
    select	*
    from	RandomSequences
    where	SortOrder = 1
    union all
    select	*
    from	RandomSequences
    where	SortOrder = 1
    union all
    select	*
    from	RandomSequences
    where	SortOrder = 1
    The result set will include three completely different records, instead of the same value three times.

    Just something to remember should you ever run into this issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It almost has to in order to process something like:
    Code:
    WITH cte (a) AS (
    SELECT a
       FROM (VALUES (1), (2), (3), (4)) AS x(a)
    )
    SELECT *
       FROM cte AS a
       CROSS JOIN cte AS b
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nice find. Very interesting behaviour - I would not have expected that either...

    Just another reason to hammer home the "don't expect order without order by!"
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •