June 23, 2009 at 1:20 am
Anybody can suggest any other methods to print 1 to 100 in SQL Server(Without creating any DB objects,Without passing any parameter)?
http://sinshith.wordpress.com/
/*DIFFERENT METHODS FOR PRINTING 1 TO 100*/
------RECURSIVE METHOD USING COMMON TABLE EXPRESSION-------
WITH CTE
AS
(
SELECT COUNT=1
UNION ALL
SELECT COUNT=COUNT+1
FROM CTE WHERE COUNT<100
)
SELECT COUNT FROM CTE
----------USING WHILE LOOP-------------------------
DECLARE @V1 INT
SET @V1=0
WHILE (@V1<100)
BEGIN
SET @V1=@V1+1
PRINT @V1
END
-----------USING CURSOR----------------------
DECLARE @TB TABLE
(
V2 INT
)
INSERT INTO @TB
SELECT 0
DECLARE @V1 INT
DECLARE CUR CURSOR
FOR SELECT V2 FROM @TB
OPEN CUR
FETCH NEXT FROM CUR INTO @V1
WHILE (@@FETCH_STATUS<>-1)
BEGIN
SET @V1=@V1+1
PRINT @V1
IF(@V1=100)
BREAK
END
FETCH NEXT FROM CUR INTO @V1
CLOSE CUR
DEALLOCATE CUR
---------USING GOTO--------------
DECLARE @V1 INT
SET @V1=0
LABEL:
SET @V1=@V1+1
PRINT @V1
IF (@V1<>100)
GOTO LABEL
June 23, 2009 at 1:33 am
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY Object_id)
FROM master.sys.columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 1:55 am
I don't think it's officially documented, but I've seen plenty of people use something like this:-
select number from master..spt_values
where type = 'p'
and number between 1 and 100
order by number
Alternatively, set up your own "Tally" table, as described in Jeff's article:-
June 23, 2009 at 1:56 am
:-D;-)
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 2:02 am
try this my friend :
WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number <= 99
)
SELECT * FROM Numbers
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 2:04 am
Oh , you took that liberty as well ...sorry I am out of answers ....
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 2:16 am
Great Answer.........with using master.sys.columns......I never tried it b4........Thank u Gail.....
June 23, 2009 at 10:44 pm
Please contribute ur ideas...I have got only one correct answer....
June 23, 2009 at 11:36 pm
hi_abhay78 (6/23/2009)
try this my friend :WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number <= 99
)
SELECT * FROM Numbers
You probably won't notice for only 100 numbers, but recursion is a form of slow RBAR. Try it for a million numbers and then try an extension of the way Gail demo'd....
[font="Courier New"]WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number <= 999999
)
SELECT * FROM Numbers
OPTION(MAXRECURSION 0)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY GETDATE())
FROM Master.sys.SysColumns sc1
CROSS JOIN Master.sys.SysColumns sc2
[/font]
As for the original question... standard interview question. The question that follows that is "Ok... tell me how it works." You should know. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2009 at 11:43 pm
Of course, there's always the Itzik method...
[font="Courier New"] CREATE FUNCTION dbo.fnItzekNumsTest(@N AS BIGINT)
RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
SELECT N FROM Nums WHERE n <= 100[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2009 at 11:50 pm
Of course, there's my favorite...
[font="Courier New"] SELECT t.N
FROM dbo.Tally t
WHERE t.N <= 100[/font]
You should Google the term "Tally Table" or click on the following link.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2009 at 6:44 am
Fastest possible implementation:
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31 UNION ALL
SELECT 32 UNION ALL
SELECT 33 UNION ALL
SELECT 34 UNION ALL
SELECT 35 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 40 UNION ALL
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 44 UNION ALL
SELECT 45 UNION ALL
SELECT 46 UNION ALL
SELECT 47 UNION ALL
SELECT 48 UNION ALL
SELECT 49 UNION ALL
SELECT 50 UNION ALL
SELECT 51 UNION ALL
SELECT 52 UNION ALL
SELECT 53 UNION ALL
SELECT 54 UNION ALL
SELECT 55 UNION ALL
SELECT 56 UNION ALL
SELECT 57 UNION ALL
SELECT 58 UNION ALL
SELECT 59 UNION ALL
SELECT 60 UNION ALL
SELECT 61 UNION ALL
SELECT 62 UNION ALL
SELECT 63 UNION ALL
SELECT 64 UNION ALL
SELECT 65 UNION ALL
SELECT 66 UNION ALL
SELECT 67 UNION ALL
SELECT 68 UNION ALL
SELECT 69 UNION ALL
SELECT 70 UNION ALL
SELECT 71 UNION ALL
SELECT 72 UNION ALL
SELECT 73 UNION ALL
SELECT 74 UNION ALL
SELECT 75 UNION ALL
SELECT 76 UNION ALL
SELECT 77 UNION ALL
SELECT 78 UNION ALL
SELECT 79 UNION ALL
SELECT 80 UNION ALL
SELECT 81 UNION ALL
SELECT 82 UNION ALL
SELECT 83 UNION ALL
SELECT 84 UNION ALL
SELECT 85 UNION ALL
SELECT 86 UNION ALL
SELECT 87 UNION ALL
SELECT 88 UNION ALL
SELECT 89 UNION ALL
SELECT 90 UNION ALL
SELECT 91 UNION ALL
SELECT 92 UNION ALL
SELECT 93 UNION ALL
SELECT 94 UNION ALL
SELECT 95 UNION ALL
SELECT 96 UNION ALL
SELECT 97 UNION ALL
SELECT 98 UNION ALL
SELECT 99 UNION ALL
SELECT 100
😀 Paul 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 24, 2009 at 8:25 am
Sinshith S Anand (6/23/2009)
Please contribute ur ideas...I have got only one correct answer....
Heh... the correct answer is "Anything that doesn't resort to RBAR or hidden RBAR."
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2009 at 4:15 am
Hello
Here is one that uses a cartesian product:
Select Ones.val + Tens.val
from (Select 0 as val
union Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
union Select 8
union Select 9) AS Ones
cross join
(Select 0 as val
union Select 10 as val
union Select 20
union Select 30
union Select 40
union Select 50
union Select 60
union Select 70
union Select 80
union Select 90) AS Tens
Where Ones.val + Tens.val >= 1
Order by Ones.val + Tens.val
Edit: I could not remember the "CROSS JOIN" keyword when I wrote the original. Updated to use that, as old syle joins irk me 🙂
June 25, 2009 at 8:36 am
Still many methods are there.....Please contribute your ideas
Viewing 15 posts - 1 through 15 (of 79 total)
You must be logged in to reply to this topic. Login to reply