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

 Get a range of numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, May 01, 2012 8:14 PM
 Old Hand Group: General Forum Members Last Login: Friday, July 12, 2013 12:53 PM Points: 313, Visits: 174
 Comments posted to this topic are about the item Get a range of numbers
Post #1293575
 Posted Wednesday, May 02, 2012 3:41 AM
 SSCrazy Group: General Forum Members Last Login: Friday, December 13, 2013 7:49 AM Points: 2,269, Visits: 765
 wouldn't this be easier to do with a recursive CTE?declare @start bigint=99;declare @end bigint=147;with x (num) as(select @start as numUNION ALL SELECT num+1 from xwhere num <@end)select * from x where num between @start and @endoption (maxrecursion 10000); MVDBA
Post #1293694
 Posted Wednesday, May 02, 2012 4:07 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 11:49 AM Points: 2,358, Visits: 7,159
 michael vessey (5/2/2012)wouldn't this be easier to do with a recursive CTE?declare @start bigint=99;declare @end bigint=147;with x (num) as(select 1 as numUNION ALL SELECT num+1 from xwhere num <@end)select * from x where num between @start and @endoption (maxrecursion 10000);Or . . .`DECLARE @start BIGINT = 97;DECLARE @end BIGINT = 250000;WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),CTE5(n) AS (SELECT 1 FROM CTE4 x, CTE4 y),CTE6(n) AS (SELECT 0 UNION ALL SELECT TOP (@end-@start) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM CTE5 x, CTE5 y)SELECT @start+nFROM CTE6WHERE @start+n <= @end;`Or. . .`DECLARE @start BIGINT = 97;DECLARE @end BIGINT = 1000000;SELECT @start+nFROM (SELECT 0 UNION ALL SELECT TOP (@end-@start) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master.sys.columns a, master.sys.columns b, master.sys.columns c) a(n)WHERE @start+n <= @end;` 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 #1293702
 Posted Wednesday, May 02, 2012 7:13 AM
 SSCrazy Group: General Forum Members Last Login: Friday, December 13, 2013 7:49 AM Points: 2,269, Visits: 765
 depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version MVDBA
Post #1293809
 Posted Wednesday, May 02, 2012 8:19 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 11:49 AM Points: 2,358, Visits: 7,159
Post #1293872
 Posted Wednesday, May 02, 2012 8:25 AM
 SSCrazy Group: General Forum Members Last Login: Friday, December 13, 2013 7:49 AM Points: 2,269, Visits: 765
 Cadavre (5/2/2012)michael vessey (5/2/2012)depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) versionActually, you should pretty much never use recursive CTEs for counting. See this article for why.run the CTE and the cross join version you posted and look at the execution plans.... then see the difference the CTE has it's place when dealing with small numbers MVDBA
Post #1293878
 Posted Wednesday, May 02, 2012 8:31 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 11:49 AM Points: 2,358, Visits: 7,159
Post #1293883
 Posted Tuesday, May 15, 2012 9:14 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, June 23, 2013 10:39 PM Points: 231, Visits: 506
 Using a table with numbers is best way
Post #1300345
 Posted Friday, May 25, 2012 6:33 AM
 SSC-Addicted Group: General Forum Members Last Login: Yesterday @ 6:28 AM Points: 465, Visits: 383
 This may be only slightly related, but thought I'd share.I used this solution for creating sequential carton labels for shipping.I created a procedure that populates a table of sequential numbers by feeding in parameters from a SSRS report. In this case I feed the beginning and ending number.CREATE PROCEDURE SEQUENTIAL(@BEG_NUM INT,@END_NUM INT)ASDECLARE @start INTDECLARE @end INTSELECT @start = @BEG_NUM, @end = @END_NUMWHILE @start <= @endbeginINSERT INTO BUR20VM.dbo.SEQUENTIAL_TABLE(CTN_NUM_SEQ)VALUES(@start)SET @start = @start + 1ENDGOI then use the resulting table results in a cross join with another table, to get the label output.I should also note, when I go to run the next sequence of numbers (for the next shipment), the SSRS report first EXECutes a second stored procedure to delete all values from the previosly created SEQUENTIAL_TABLE, and then re-populates with the new sequence of numbers.I think this is a good illustration of a real world application for the sequential table.
Post #1306509

 Permissions