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;

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
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.

ste5anSenior 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.
0
Mike EghtebasDatabase 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)

Open in new window


;With cte
As
(
 Select * From Table1     -- what is replacing inside is what I want to make sense of
)
0
Mike EghtebasDatabase 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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anoo S PillaiCommented:
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 */ 

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Anoo S PillaiCommented:
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)

Open in new window


CTE can have multiple select statement and subsequent SELECT statements can refer previous outputs. This is neatly used in this logic
0
Anoo S PillaiCommented:
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;

Open in new window

Think this is useful to get an idea on multi statement CTE..
0
Anoo S PillaiCommented:
Huh !!!

Found out this link ( which refers to the previous version of the code by the same Author )  at
virtual-auxiliary-table-numbers
0
ste5anSenior 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;

Open in new window

 has exactly the same execution plan as
SELECT * FROM   (SELECT * FROM sys.tables)  A CROSS JOIN  (SELECT * FROM sys.tables) A2;

Open in new window

. 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.
0
Mike EghtebasDatabase 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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.