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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 23, 2009 at 1:33 am

  #1013758

  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

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  June 23, 2009 at 1:55 am

  #1013768

  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/

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  June 23, 2009 at 1:56 am

  #1013769

  :-D;-)

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

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  June 23, 2009 at 2:02 am

  #1013770

  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)

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  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)

• Sinshith S Anand

  SSChasing Mays

  Points: 605

  More actions

  June 23, 2009 at 2:16 am

  #1013778

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

• Sinshith S Anand

  SSChasing Mays

  Points: 605

  More actions

  June 23, 2009 at 10:44 pm

  #1014419

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

• Jeff Moden

  SSC Guru

  Points: 1004617

  More actions

  June 23, 2009 at 11:36 pm

  #1014432

  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)
  

• Jeff Moden

  SSC Guru

  Points: 1004617

  More actions

  June 23, 2009 at 11:43 pm

  #1014433

  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)
  

• Jeff Moden

  SSC Guru

  Points: 1004617

  More actions

  June 23, 2009 at 11:50 pm

  #1014436

  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)
  

• Paul White

  SSC Guru

  Points: 150468

  More actions

  June 24, 2009 at 6:44 am

  #1014604

  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

• 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