This question comes across as a straight forward question but can prove lethal to gauge the turnaround time. It also checks the ability of the person to reach the destination when virtually all the doors are closed.
Well the complete question is
" How to generate values from 1 to 1000 without using WHILE loop and Cursor ? and Using single SELECT or block of code"
The question became interesting with the last part because most of us would have thought about using loop/cursor but that is forbidden.. and we also have to use single SELECT or block of code ..
Okay.. What does that mean ? Is it some kind of a clue to answer this question ?
YES, it is. How?
Let us see
I hope you guys are familiar with recursive CTEs because that is what I have used in the following query
;WITH CTE AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE
)
SELECT * FROM CTE
This query did produce the numbers but from 1 to 101 only and then it failed with following error
Which suggests that CTE can reference itself for 100 times only.. but we want to loop it for 1000 ( 999 ) times to produce the desired result .. How do I do that ?
Microsoft has been kind enough to provide the mechanism to override the default value for the CTE recursion and it can be done using OPTION (MAXRECURSION number)
After this query becomes
;WITH CTE AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE
WHERE NUM < 1001
)
SELECT * FROM CTE
OPTION (MAXRECURSION 1000)
And there you have your numbers from 1 to 1000
What if we want to produce number till 100000 would it be still possible with CTE ?
;WITH CTE AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE
WHERE NUM < 1001
)
SELECT * FROM CTE
OPTION (MAXRECURSION 100000)
Ahha..The answer is NO.. It is not possible. Because CTE fails with the following error which suggest recursion can be extended up to certain limit only.
Msg 310, Level 15, State 1, Line 10
The value 100000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.
Well the complete question is
" How to generate values from 1 to 1000 without using WHILE loop and Cursor ? and Using single SELECT or block of code"
The question became interesting with the last part because most of us would have thought about using loop/cursor but that is forbidden.. and we also have to use single SELECT or block of code ..
Okay.. What does that mean ? Is it some kind of a clue to answer this question ?
YES, it is. How?
Let us see
I hope you guys are familiar with recursive CTEs because that is what I have used in the following query
;WITH CTE AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE
)
SELECT * FROM CTE
This query did produce the numbers but from 1 to 101 only and then it failed with following error
Which suggests that CTE can reference itself for 100 times only.. but we want to loop it for 1000 ( 999 ) times to produce the desired result .. How do I do that ?
Microsoft has been kind enough to provide the mechanism to override the default value for the CTE recursion and it can be done using OPTION (MAXRECURSION number)
After this query becomes
;WITH CTE AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE
WHERE NUM < 1001
)
SELECT * FROM CTE
OPTION (MAXRECURSION 1000)
And there you have your numbers from 1 to 1000
What if we want to produce number till 100000 would it be still possible with CTE ?
;WITH CTE AS
(
SELECT 1 AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE
WHERE NUM < 1001
)
SELECT * FROM CTE
OPTION (MAXRECURSION 100000)
Ahha..The answer is NO.. It is not possible. Because CTE fails with the following error which suggest recursion can be extended up to certain limit only.
Msg 310, Level 15, State 1, Line 10
The value 100000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.
Good interesting stuff Chetan.
ReplyDeleteThanks, Tomsy.
Delete