Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get a range of numbers Expand / Collapse
Author
Message
Posted Tuesday, May 1, 2012 8:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
Comments posted to this topic are about the item Get a range of numbers
Post #1293575
Posted Wednesday, May 2, 2012 3:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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 num
UNION ALL
SELECT num+1 from x
where num <@end
)
select * from x where num between @start and @end
option (maxrecursion 10000);


MVDBA
Post #1293694
Posted Wednesday, May 2, 2012 4:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,379, Visits: 7,564
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 num
UNION ALL
SELECT num+1 from x
where num <@end
)
select * from x where num between @start and @end
option (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+n
FROM CTE6
WHERE @start+n <= @end;

Or. . .
DECLARE @start BIGINT = 97;
DECLARE @end BIGINT = 1000000;

SELECT @start+n
FROM (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 learn

For 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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1293702
Posted Wednesday, May 2, 2012 7:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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 2, 2012 8:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,379, Visits: 7,564
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) version


Actually, you should pretty much never use recursive CTEs for counting. See this article for why.



Not a DBA, just trying to learn

For 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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1293872
Posted Wednesday, May 2, 2012 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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) version


Actually, 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 2, 2012 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,379, Visits: 7,564
michael vessey (5/2/2012)
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) version


Actually, 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


Did you have a gander at Jeff Moden's article that I linked? --> http://www.sqlservercentral.com/articles/T-SQL/74118/



Not a DBA, just trying to learn

For 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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1293883
Posted Tuesday, May 15, 2012 9:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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

GulliMeel

Finding top n Worst Performing queries
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw
Post #1300345
Posted Friday, May 25, 2012 6:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 531, Visits: 448
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
)
AS

DECLARE @start INT
DECLARE @end INT

SELECT @start = @BEG_NUM, @end = @END_NUM

WHILE @start <= @end
begin
INSERT INTO BUR20VM.dbo.SEQUENTIAL_TABLE(CTN_NUM_SEQ)
VALUES(@start)
SET @start = @start + 1
END
GO

I 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse