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: Wednesday, February 4, 2015 12:15 PM Points: 47, Visits: 19
 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-Forever Group: General Forum Members Last Login: Yesterday @ 11:30 PM Points: 42,081, Visits: 39,471
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1254455
 Posted Monday, February 20, 2012 1:09 AM
 Ten Centuries Group: General Forum Members Last Login: Saturday, November 12, 2016 6:57 PM Points: 1,006, Visits: 1,898
 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-Forever Group: General Forum Members Last Login: Yesterday @ 11:30 PM Points: 42,081, Visits: 39,471
 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." Helpful 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: Wednesday, September 24, 2014 9:32 AM Points: 2, Visits: 89
 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: Tuesday, December 6, 2016 8:35 AM Points: 2,492, Visits: 8,430
 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. Forever 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 nolockCraig Wilkinson - Software EngineerLinkedIn
Post #1254691
 Posted Monday, February 20, 2012 6:12 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 11:30 PM Points: 42,081, Visits: 39,471
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1254692

 Permissions