## List of all numbers between low and high

 Author Message texpic Hall of Fame Group: General Forum Members Points: 3152 Visits: 463 What is the best way to get a list of all numbers between low and high numbers? Thanks.Actually >= low, <= high`CREATE TABLE #TempBet(High int, Low int)INSERT INTO #TempBet (High, Low)SELECT 1, 3 UNION ALLSELECT 2, 3 UNION AllSELECT 5, 8RESULTLow High Bet1 3 11 3 21 3 32 3 22 3 35 8 55 8 65 8 75 8 8` texpic Hall of Fame Group: General Forum Members Points: 3152 Visits: 463 Reworked another forum solution`DROP TABLE #TempBetCREATE TABLE #TempBet(Id int, Low int, High int)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 1, 3 UNION ALLSELECT 2, 2, 3 UNION AllSELECT 3, 5, 8; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, Result + 1FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result` texpic Hall of Fame Group: General Forum Members Points: 3152 Visits: 463 When I use larger numbers I get this error "types don't match between the anchor and the recursive part"`CREATE TABLE #TempBet(Id int, Low numeric, High numeric)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 10000000000, 30000000000 UNION ALLSELECT 2, 20000000000, 30000000000 UNION AllSELECT 3, 50000000000, 80000000000; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, Result + 1FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result` demonfox Hall of Fame Group: General Forum Members Points: 3665 Visits: 1192 ` cast ((Result+1) as numeric)`If you use Int ,in this case BigInt , then you won't be seeing this error... ~ demonfox___________________________________________________________________Wondering what I would do next , when I am done with this one Jeff Moden SSC Guru Group: General Forum Members Points: 512188 Visits: 44311 texpic (8/4/2012)When I use larger numbers I get this error "types don't match between the anchor and the recursive part"`CREATE TABLE #TempBet(Id int, Low numeric, High numeric)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 10000000000, 30000000000 UNION ALLSELECT 2, 20000000000, 30000000000 UNION AllSELECT 3, 50000000000, 80000000000; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, Result + 1FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result`The first item will produce a list of 20 Billion numbers, the second 10 Billion, and the 3rd 30 Billion for a total of 60 Billion numbers (rows).With that in mind, I have to ask... what is the purpose of this exercise and what do you expect to do with 60 Billion rows? --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs texpic Hall of Fame Group: General Forum Members Points: 3152 Visits: 463 Jeff:It was a bad example. The number of combinations between the true numbers is about 50,000 each. I'll try it with 50,000 each on the span and see if I get the error still. texpic Hall of Fame Group: General Forum Members Points: 3152 Visits: 463 I have a reference table that I was given that has 50,000 records. It has ranges between two 11-digit numbers. I need to makes sure there are no overlaps. I was going to try and get the list of all the numbers in between the two ranges, then simply make sure they were all distinct.For this example I cleaned up the ranges so they were less than 50,000 each; overlap is on purpose between the 2 lines. Changed all the numeric to bigint.New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."`-- DROP TABLE #TempBetCREATE TABLE #TempBet(Id int, Low bigint, High bigint)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 10000050000, 10000080000 UNION ALLSELECT 2, 10000070000, 10000090000 UNION AllSELECT 3, 50000050000, 50000090000 UNION AllSELECT 4, 50000060000, 50000070000 ; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, CAST(Result + 1 as bigint)FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result` dwain.c SSC-Forever Group: General Forum Members Points: 44623 Visits: 6431 You are probably better off using a Tally table for this:`CREATE TABLE #TempBet(High int, Low int)INSERT INTO #TempBet (Low, High)SELECT 1, 3 UNION ALLSELECT 2, 3 UNION AllSELECT 5, 8;WITH Tally (n) AS ( SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)SELECT High, Low, Bet=nFROM #TempBetCROSS APPLY ( SELECT n FROM Tally WHERE n BETWEEN Low AND High) aDROP TABLE #TempBet` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables demonfox Hall of Fame Group: General Forum Members Points: 3665 Visits: 1192 texpic (8/5/2012) New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."use MAXRECURSION hint.check this out:http://msdn.microsoft.com/en-us/library/ms186243(v=SQL.105).aspx ~ demonfox___________________________________________________________________Wondering what I would do next , when I am done with this one Jeff Moden SSC Guru Group: General Forum Members Points: 512188 Visits: 44311 texpic (8/5/2012)I have a reference table that I was given that has 50,000 records. It has ranges between two 11-digit numbers. I need to makes sure there are no overlaps. I was going to try and get the list of all the numbers in between the two ranges, then simply make sure they were all distinct.For this example I cleaned up the ranges so they were less than 50,000 each; overlap is on purpose between the 2 lines. Changed all the numeric to bigint.New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."`-- DROP TABLE #TempBetCREATE TABLE #TempBet(Id int, Low bigint, High bigint)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 10000050000, 10000080000 UNION ALLSELECT 2, 10000070000, 10000090000 UNION AllSELECT 3, 50000050000, 50000090000 UNION AllSELECT 4, 50000060000, 50000070000 ; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, CAST(Result + 1 as bigint)FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result`A recursive CTE may be a bit of overkill for this. Do you need to ensure there are no gaps, as well? --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs