# Function help

Q1: Could you please add some comments to the function code below showing what this function is doing?
Q2: Also, please give me a link explaining (not a table value function) but from lines 7 - 15 workings?

``````use TSQL2012
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;
``````
LVL 34
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DeveloperCommented:
It returns a number tally where you can specify the lower and upper bound auf the number range.

It's using Common Table Expressions to cross join (Cartesian product, exponential growth of rows)  sets.
Database and Application DeveloperAuthor Commented:
Hi ste5an,

I am fairly comfortable with CTEs. But the construct below is new to me. Is there a place I can read more about this kind of construct not so much CTE?
``````WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
``````

;With cte
As
(
Select * From Table1     -- what is replacing inside is what I want to make sense of
)
Database and Application DeveloperAuthor Commented:
It looks like sort of nested select statement. Never used one. Do you where I can read more about it?

What is the title of this method of building data? Recursive select perhaps?
Commented:
Embedding comments in the code section.

``````CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
/* This function will return integers starting with the parameter @low and ending with parameter @high
Sample function call - select * from dbo.GetNums ( 10 , 30 )
Logic - Generate sequential numbers between 1 and 4294967296, then select only the values which
are between the supplied parameters @low and @high */
RETURN

WITH /* common table expression start - Refer https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx */

/* (VALUES(1),(1))
this above given is a table value constructor which would give two rows with values 1 , 1
Please refer https://msdn.microsoft.com/en-us/library/dd776382.aspx for Table Value Constructor
(VALUES(1),(1)) AS D(c)) -> Here D is the table alias and 'c' is the column name.
so in essence "(VALUES(1),(1)) AS D(c))" would retun a table named 'D' with column name 'c' with two rows
with values 1 in both the rows ,please run and verify SELECT c FROM (VALUES(1),(1)) AS D(c)	*/
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c))
-- SELECT * FROM L0 -- will give you 2 rows with value 1 in it
, L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B)
-- SELECT * FROM L1 -- simple CROSS JOIN - will give you 4 rows with value 1 in it,
, L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B)
-- SELECT * FROM L2 -- -- simple CROSS JOIN - will give you 16 rows with value 1 in it,
, L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B)
--- SELECT * FROM L3 -- -- simple CROSS JOIN - will give you 16*16= 256 rows with value 1 in it,
, L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B)
-- SELECT * FROM L4 -- -- simple CROSS JOIN - will give you 256*256= 65536 rows with value 1 in it,
, L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B)
-- SELECT * FROM L5  -- -- simple CROSS JOIN - will give you 65536*65536= 4294967296 rows with value 1 in it,

/* Now from this 4294967296 , select only rows which are coming in between the function parameters @low and @high */
/* Each row is converted to its sequential number equivalent suing ROW_NUMBER()								   */
/* ie. First row with value 1, secnd rows with value 2, third row with value 3 etc                                */
/* ROW_NUMBER() - details available at https://msdn.microsoft.com/en-us/library/ms186734.aspx                     */
/* for ROW_NUMBER(), a madatory ORDER BY clause is needed, hence a dummy SELECT NULL is given as an ORDER BY      */
/* The above is to tweak T-SQL to use ROW_NUMBER for this purpose												   */
, Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
-- SELECT * FROM Nums -- after ROW_NUMBER() is applied we have a sequnce of numbers from 1 to 4294967296
/* Now we need to select only the integers that is coming in between the supplied @low and @high                   */

SELECT @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;
/* The above logic is obvious - syntacx is at https://technet.microsoft.com/en-us/library/ms188385(v=sql.110).aspx */
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
``````WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
``````

CTE can have multiple select statement and subsequent SELECT statements can refer previous outputs. This is neatly used in this logic
Commented:
Following is based on a query ( edited ) from "High performance T-SQL using Window functions"

``````WITH
CustAggregates AS ( SELECT custid, SUM(val) AS sumval, AVG(val) AS avgval
FROM Sales.OrderValues
GROUP BY custid ),
GrandAggregates AS ( SELECT SUM(val) AS sumval, AVG(val) AS avgval
FROM Sales.OrderValues  )
SELECT O.orderid, O.custid, O.val,
O.val / CA.sumval AS pctcust,
O.val - CA.avgval AS diffcust,
O.val / GA.sumval AS pctall,
O.val - GA.avgval AS diffall
FROM Sales.OrderValues AS O
JOIN CustAggregates AS CA
ON O.custid = CA.custid
CROSS JOIN GrandAggregates AS GA;
``````
Think this is useful to get an idea on multi statement CTE..
Commented:
Huh !!!

Found out this link ( which refers to the previous version of the code by the same Author )  at
virtual-auxiliary-table-numbers
Senior DeveloperCommented:
A CTE is exactly what it is named after an expression. Each name AS () is one CTE. And you can have as much as you want of them in one WITH block.

A normal CTE is not a sub-query. It is just syntactic sugar to make nested queries more readable. They are expanded at compile time in place. This means for example:

``````WITH A  AS (SELECT * FROM sys.tables) SELECT * FROM A A1 CROSS JOIN A A2;
``````
has exactly the same execution plan as
``````SELECT * FROM   (SELECT * FROM sys.tables)  A CROSS JOIN  (SELECT * FROM sys.tables) A2;
``````
. That's what I meant with expanded. The only optimization which happens is a table spool/lazy spool, but this happens also in both cases.

This is also the reason, while optimizing queries, we still have to test with table variables and/or temporary tables as intermediate result materialization to improve speed.

A recursive CTE is a little bit different. Cause you have a fix anchor SELECT UNION ALLed with the recursion SELECT). Here is more done by the compiler and optimizer than just expanding.
Database and Application DeveloperAuthor Commented:
ste5an,

My apology for not seeing nested CTEs in lines 7-15. Yes indeed these are each a CTE separated with a coma. What was totally new to me was construct like SELECT c FROM (VALUES(1),(1)) AS D(c).

Anoo,
I really appreciate for the detailed explanations. Now I totally understand what is going on. With your help now I am able to move forward.

Thanks,

Mike
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.