Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TSQL that lists all numbers from 1..100 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, February 18, 2012 12:22 PM
 SSC Rookie Group: General Forum Members Last Login: Friday, April 12, 2013 6:04 AM Points: 47, Visits: 17
 Comments posted to this topic are about the item TSQL that lists all numbers from 1..100
Post #1254365
 Posted Sunday, February 19, 2012 12:01 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 3:55 AM Points: 34,534, Visits: 28,697
 Gosh, Eli. Thanks for sharing but please consider the following, instead. It's faster, still doesn't require access to a table, can produce a much larger range of numbers, and takes a whole lot fewer rows of code especially for what it does.`WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10E1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E3 or 10000 rows E8(N) AS (SELECT 1 FROM E4 a, E4 b), --10E4 or 100000000 rowsE16(N) AS (SELECT 1 FROM E8 a, E8 b) --10E16 or more rows than you can shake a stick atSELECT TOP (@DesiredRowCount) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16;` --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1254455
 Posted Monday, February 20, 2012 1:09 AM
 Say Hey Kid Group: General Forum Members Last Login: Friday, November 29, 2013 6:57 PM Points: 709, Visits: 1,436
 Who was the first person to come up with this (Jeff's) idea in the context of SQL Server? Was it Itzik Ben-Gan? One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.Bertrand Russell
Post #1254573
 Posted Monday, February 20, 2012 5:55 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 3:55 AM Points: 34,534, Visits: 28,697
 GPO (2/20/2012)Who was the first person to come up with this (Jeff's) idea in the context of SQL Server? Was it Itzik Ben-Gan?Yes, it was Ben-Gan. He used a binary set of CTE's instead of a decimal set like many of us do here. If you'd like to see a comparison of some methods for counting, please see the following article.http://www.sqlservercentral.com/articles/T-SQL/74118/ --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1254681
 Posted Monday, February 20, 2012 6:03 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, August 23, 2013 12:47 PM Points: 2, Visits: 76
 Try this one:with t as (select 1 x union all select x + 1 from t where x < 100)select x from t
Post #1254686
 Posted Monday, February 20, 2012 6:12 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 2:34 AM Points: 2,358, Visits: 7,138
 ThomasGr (2/20/2012)Try this one:with t as (select 1 x union all select x + 1 from t where x < 100)select x from tRead the article that Jeff pointed at to see why that isn't a good idea. Not a DBA, just trying to learnFor better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockLinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1254691
 Posted Monday, February 20, 2012 6:12 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 3:55 AM Points: 34,534, Visits: 28,697
 ThomasGr (2/20/2012)Try this one:with t as (select 1 x union all select x + 1 from t where x < 100)select x from tThomas, read the article at the link I provided in my last post and see why that's even worse than the original suggestion on this thread. Certainly, it's one of the worse ways of counting. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1254692

 Permissions