printing 1 to 100 in SQL Server as a batch....

Question

1
2
3
4
5
6
Next

printing 1 to 100 in SQL Server as a batch....

Sinshith S Anand
SSChasing Mays

Points: 605
More actions
June 23, 2009 at 1:20 am

#103770

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


          
          
           
            
             
              Jeff Moden
             
            
            
             
             
             
              
              
             




    

             
              SSC Guru
             
             
              Points: 1004617
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                June 24, 2009 at 8:25 am
               
              
              
               
               
               
                #1014682
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               
                
                 
                  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
               
               
               
                RBAR
               
               is pronounced "
               
                ree-bar
               
               " and is a "
               
                Modenism
               
               " for
               
                R
               
               ow-
               
                B
               
               y-
               
                A
               
               gonizing-
               
                R
               
               ow.
               

               
                First step towards the paradigm shift of writing Set Based code:
               
               

               
                ________
               
               
                Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
               
               
                
                 Change is inevitable... Change for the better is not.
                
                

               
               
               
                Helpful Links:
               
               

               
                How to post code problems
               
               

               
                How to Post Performance Problems
               
               

               
                Create a Tally Function (fnTally)
               
               

               
               
              
              
              
             
             
              
             
            
           
          
          
          
          
           
          
          
           
           
           
            
            
            
             
             
             
              jdurandt
             
            
            
             
             
             
              
              
             
             
              SSChasing Mays
             
             
              Points: 628
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                June 25, 2009 at 4:15 am
               
              
              
               
               
               
                #1015177
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               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 🙂
              
             
             
            
           
          
          
          
          
          
           
            
             
              Sinshith S Anand
             
            
            
             
             
             
              
              
             
             
              SSChasing Mays
             
             
              Points: 605
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                June 25, 2009 at 8:36 am
               
              
              
               
               
               
                #1015345
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               Still many methods are there.....Please contribute your ideas

Viewing 15 posts - 1 through 15 (of 80 total)

1
2
3
4
5
6
Next


          
           
            
             
             
            
            
             
              You must be logged in to reply to this topic.
              
               Login to reply

Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Answer 2

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:-

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

Answer 3

hi_abhay78

SSCertifiable

Points: 6265

June 23, 2009 at 1:56 am

#1013769

:-D;-)

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Answer 4

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)

Answer 5

hi_abhay78

SSCertifiable

Points: 6265

June 23, 2009 at 2:04 am

#1013771

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)

Answer 6

Sinshith S Anand

SSChasing Mays

Points: 605

June 23, 2009 at 2:16 am

#1013778

Great Answer.........with using master.sys.columns......I never tried it b4........Thank u Gail.....

Answer 7

Sinshith S Anand

SSChasing Mays

Points: 605

June 23, 2009 at 10:44 pm

#1014419

Please contribute ur ideas...I have got only one correct answer....

Answer 8

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

RBAR is pronounced " ree-bar " and is a " Modenism " for R ow- B y- A gonizing- R ow.
First step towards the paradigm shift of writing Set Based code:
________ Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Answer 9

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

RBAR is pronounced " ree-bar " and is a " Modenism " for R ow- B y- A gonizing- R ow.
First step towards the paradigm shift of writing Set Based code:
________ Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Answer 10

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

RBAR is pronounced " ree-bar " and is a " Modenism " for R ow- B y- A gonizing- R ow.
First step towards the paradigm shift of writing Set Based code:
________ Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Answer 11

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 All articles available on SQL.kiwi @SQL_Kiwi

Answer 12

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

RBAR is pronounced " ree-bar " and is a " Modenism " for R ow- B y- A gonizing- R ow.
First step towards the paradigm shift of writing Set Based code:
________ Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Answer 13

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 🙂

Answer 14

Sinshith S Anand

SSChasing Mays

Points: 605

June 25, 2009 at 8:36 am

#1015345

Still many methods are there.....Please contribute your ideas