Cross Apply with recursive CTE

Transact-SQL
1 
;with cte as (
	select a = 1
	union all
	select a = a + 1
	from cte
	where a < 101
)
select
	id = newid()
from
  	cte 
	cross apply cte cte2

That results in 10201 rows. Change a < 101 to a < 100 and it results in 10000 rows.

Why?

2

Your cte will give 101 records.
and join/cross apply with him ( with 101 records) will give you 10201
101 * 101 = 10201

1 Like
3

Thanks. I'm trying to internalize the Cartesian product.

100 * 100 = 10,000
The 201 seems 'off' for some reason.
At first I was thinking a CROSS APPLY would yield 10,0XX.
101 * 101 seems like it would be 10,202.

4

The first SELECT in the cte is one row, the second is 100 rows, the UNION ALL puts them together for 101 rows.

To explain / correct, change the WHERE clause to:

where a + 1 < 101

when a = 100 -- which is less than 101 -- the SELECT "a + 1" will return the value 101. Since you don't want that returned, you need to change the WHERE accordingly.

1 Like
5

It doesn't matter what you're doing, using an rCTE (Recursive CTE) to create a sequence is actually slower and much more resource intensive that writing a proper WHILE Loop to do the same thing. rCTEs are an insidious form of "Hidden RBAR" and are NOT set based behind the scenes. Please see the following article for much more detail. rCTE's shouldn't be used even for tiny rowcounts.

http://www.sqlservercentral.com/articles/T-SQL/74118/

The article includes 3 other methods for generating sequences that blow the doors off of rCTEs in virtually every aspect.

1 Like
6

JeffModen hit the nail on the head. I'm an old semi-DBA from 2005 days and looking at the new ways of doing things.

The 'anchor' and UNION ALL seems a bit kludgy. I'm still uncertain about the PARTITION (ORDER BY) stuff. When to use a CTE vs. nested SELECT. Cross Apply used to be something to avoid.

7

I need some help on this. It works and it's fast, but it's not entirely all correct. Is this the best way to do this?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	Andrew B. Brown
	9/9/2017

	Numbers up to max int

	select * from dbo.Numbers(2, 1000)
*/

Create Function dbo.Numbers
(	
	@from int,
	@to int
) RETURNS TABLE 
AS
RETURN 
(
	With rows1 As (
		Select col From (
		Values
		(@from),
		(@from + 1),
		(@from + 2),
		(@from + 3),
		(@from + 4),
		(@from + 5),
		(@from + 6),
		(@from + 7),
		(@from + 8),
		(@from + 9)) anchor(col)
    )
	, rows2 AS (SELECT product.col FROM rows1 product CROSS JOIN rows1)
	, rows3 AS (SELECT product.col FROM rows2 product CROSS JOIN rows2)
	, rows4 AS (SELECT product.col FROM rows3 product CROSS JOIN rows3)
	, rows5 AS (SELECT product.col FROM rows4 product CROSS JOIN rows4)
	, rows6 AS (SELECT Row_Number() OVER (ORDER BY col) col FROM rows5)

	Select TOP (@to - @from + 1)
        Number = col-1 + @from
    From rows6
)

    GO
8

Having a static tally table is faster:

create table dbo.tally100k(number int primary key);

insert into dbo.tally100k
select row_number() over(order by (select null))-1
 from             (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t1(n)
      cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t2(n)
      cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t3(n)
      cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t4(n)
      cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t5(n)
;

create function dbo.numbers(	
   @from int
  ,@to int
) returns table as
   return (select number
             from dbo.tally100k
            where number between @from and @to
          )
;

But if you want to create numbers on the fly, this should work:

create function dbo.numbers(	
   @from int
  ,@to int
) returns table as
   return (select top(@to-@from+1)
                  @from+row_number() over(order by (select null))-1 as number
             from              (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t1(n)
                   cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t2(n)
                   cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t3(n)
                   cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t4(n)
                   cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t5(n)
          )
;
1 Like
9

Combining with your approach...

ALTER Function [dbo].[Numbers]
(	
	@count int
) RETURNS TABLE 
AS
RETURN 
(
	with cte as (
		select n = 1
		from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n)
	)
	select
		top(@count)
		Number = Row_Number() over (order by (select null))
	from
		     		cte a	--10
		cross apply cte b	--100
		cross apply cte c	--1000
		cross apply cte d	--10000
		cross apply cte e	--100000
		cross apply cte f	--1000000
		cross apply cte g	--10000000
		cross apply cte h	--100000000
)
10

Consider the following... and the ORDER BY is "no cost" in this case but still provides the guarantee of the output order and the WITH SCHEMABINDING helps prevent spooling, as well

 CREATE FUNCTION dbo.fnTally
        (@Max INT)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN WITH cte(N) AS 
(SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
 SELECT TOP (@Max) N = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
   FROM cte a, cte b, cte c, cte d, cte e, cte f, cte g, cte h
  ORDER BY N
;
1 Like