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 12»»

Incrementing Values Expand / Collapse
Author
Message
Posted Thursday, December 24, 2009 12:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 11, 2011 12:49 AM
Points: 69, Visits: 179
Hello,

I have a range of values 12000 - 13000

In sql I would like to reproduce the values between the two figures i.e.
12000,12001,12002 etc up to 13000 and display them.

I have the following though it does'nt work very well : -

declare int @batch;

if batchnum => 1000 and batchnum <= 1500 then


batch + 1;

else

0

Post #838876
Posted Thursday, December 24, 2009 12:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:25 AM
Points: 1,262, Visits: 3,419
First of all your operator here is wrong "=>" it must be >= or <=


So you declared the parameter but you didn't use it!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #838881
Posted Thursday, December 24, 2009 1:06 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: Tuesday, August 2, 2011 3:36 AM
Points: 579, Visits: 1,803
If the difference between start and end is within the limit of 2047, then this is 'one' of the method,

Declare @start int
SET @start = 12000
Declare @End int
SET @end = 13000

SELECT @start + t.number FROM
Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND (@end - @start)


You should read this link by the way, (very useful)

http://www.sqlservercentral.com/articles/T-SQL/62867/


---------------------------------------------------------------------------------
Post #838889
Posted Thursday, December 24, 2009 2:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
Nabha is spot on and, if you check out the link he posted, you'll find the "Tally" table is a pretty nice replacement for the spt_Values table and has a much wider range. (Thanks for the article referral, Nabha )

Shifting gears...

There are a dozen or so ways to spawn such "incremental numbers". In SQL Server 2005, the following is one of the least resource intensive (virtually 0 reads, highly competitive CPU times, no writes (including the log file), and no "disk stingy" DBA's getting all bent out of shape ) although, as the comments in the code indicate, I'll usually use a real Tally table because I'm stuck in the SQL Server 2000 world a lot...

 CREATE FUNCTION dbo.TallyRange
/****************************************************************************************
Purpose:
Given a starting integer and an ending integer, generate the inclusive range of integers
between those two values in the same "direction" as those two values.

Note to DBA's:
There is no recursion or other non set-based forms of RBAR in this code. The function
itself is a high speed "inline table valued function" recognized for it's very high
performance and will generally only be used once in any given query in the FROM clause
as if it were an actual table. It causes no reads, no writes, no log growth, and no
performance problems.

Performance:
On most machines, this method takes < 1 second of CPU time to generate a million rows
of joinable incremental integers and close to 0 milliseconds to generate 8,000 rows
for use in such areas as splitting VARCHAR(8000).

Usage:
SELECT N FROM dbo.TallyRange(@Start, @End) --where @Start and @End are INT values.

Programmer's Notes.
1. Will always count in direction of start value to end value.
2. In any case, you should use an ORDER BY in the outer query to quarantee
the order you want.
3. 99.999% of the time, I'll use a permanent Tally table of 11,000 rows because it
meets/exceeds most of my needs. This function was developed because of a need
to generate some very large numbers (1 Billion was the max) and this method
does NOT cause the log file to grow whereas more than one x-join of tables
does. The initial run of multiple x-joins to get to a Billion rows caused
the log file of the working DB to grow well over 40GB. This function does not.

Credits:
Original concept by Itzik Ben-Gan and company with the alternate ideas made by many
including the following (in alphabetical order):

Jeff Moden (myself)
Lynn Pettis (see article at http://www.sqlservercentral.com/articles/T-SQL/67899/)
Matt Miller
Michael Valentine Jones
Peter Larrson
R. Barry Young

Revision History:
Rev 00 - 20 Dec 2008 - Jeff Moden
- Initial creation.
Rev 01 - 21 Dec 2008 - Jeff Moden
- Made inputs "reversible" just to be "forgiving".
Rev 02 - 08 May 2009 - Jeff Moden
- Changed from Base 2 to Base 10 notation for slight performance
- gain, made changes ("E" notation for CTE names) for the sake of
- explainability, and added "credits" for those involved in a race
- on the forum using different methods that exposed the slight
- performance gain.
Rev 03 - 20 Jun 2009 - Jeff Moden
- Remove a particular ANSI join type.
Rev 04 - 23 Sep 2009 - Jeff Moden
- Added Lynn Pettis' link to his article to credits.
Rev 05 - 26 Sep 2009 - Jeff Moden
- Moved column aliases out of the SELECTs to the CTE name
- declarations and removed "AS" from the alias names for the
- joined CTE references to further condense the code.
****************************************************************************************/
--===== Declare I/O parameters
(
@Start INT,
@End INT
)
RETURNS TABLE
AS
RETURN ( --======= Use multiple "cascaded" joined CTE's to generate numbers up to 10E16.
-- Note that the SIGN function is used to allow the inputs "reversible"
-- and ABS is used to always have a positive number for the TOP number
-- of rows to generate. The +1 is to makeup for the subtraction loss...
-- for example, counting from 0 to 10 is actually 11 counts, not 10 as
-- some would expect.
WITH E01(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), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) * SIGN(@End - @Start) FROM E16)
--===== Final SELECT uses a TOP "reflection" back on the CTE's to limit rows
-- calculated by the CTE's to only those needed.
SELECT TOP (ABS(@End - @Start) + 1)
N + @Start + SIGN(@Start - @End) AS N
FROM cteTally
)

Once you have such a function built, then problems like yours become child's play...
have a range of values 12000 - 13000

In sql I would like to reproduce the values between the two figures i.e.
12000,12001,12002 etc up to 13000 and display them.

DECLARE @Start INT,
@End INT;

SELECT @Start = 12000,
@End = 13000;

SELECT N
FROM dbo.TallyRange(@Start,@End)

Now... if you're one of those poor buggers that's working on someone else's system and the DBA won't allow you to create a function for some reason and won't create it for you, write back because we're not out of tricks yet.

By the way... you really do need to read the article Nabha pointed you to... quite literally, it'll change your life. It did mine and that's why I wrote that article... passing it forward.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #839066
Posted Thursday, December 24, 2009 2:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:25 AM
Points: 1,262, Visits: 3,419
Jeff really nice job here ...great function!

Once again I retrieve the same results from this statement:

SELECT NAME FROM T-SQL_MASTERS WHERE NAME LIKE 'J%'

The result is;
Jeff Moden - sorry ah!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #839068
Posted Thursday, December 24, 2009 3:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
Heh... and for those who are mildly curious as to what Rev 03 may have been... I ran into a human stone wall who would not allow code to be promoted if the words CROSS JOIN appeared anywhere in the code including comments. To further obfuscate the fact that this little slice of computational heaven is an exercise of cross-joins on steriods, I took out the "AS" for the CTE aliases in the FROM clauses in a later change. So far, it's gotten by some pretty tough DBA's. The really tough ones won't allow any type of UDF, either. Inline code normally works for those good folks.

Then there are the ones that actually read code before they put it into their system... that usually costs me because then I have to take them and their boss out for a nice, juicy, high velocity, pork chop dinner and explain the facts of life to them and the reason for the obfuscated cross joins.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #839070
Posted Thursday, December 24, 2009 3:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
Dugi, thanks for the kudo but it's not mine alone... take a look at the "credits" on that thing... lots of folks had some great ideas that improved the great idea that Itzik originally came up with.

Oh... and you may actually have a "bad" copy if you copied the code earlier, Dugi. Much to my mortification, I originally posted an old copy that still had the "word" Cross-Join in one of the comments that the "stone wall" called me out on. I put the correct copy up now.

As a side bar, I really need to spend some time organizing my code snippets.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #839078
Posted Thursday, December 24, 2009 3:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
Heh... like Jackie Chan's Uncle says in the cartoon, "And one mo ting!"

There is a way to totally obfuscate the cross-joins if you ever need to do such a thing... here's that part of the code should you even have the need...

        WITH E01(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), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a INNER JOIN E01 b ON a.N = b.N), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a INNER JOIN E02 b ON a.N = b.N), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a INNER JOIN E04 b ON a.N = b.N), --100,000,000 or 10E08 rows
E16(N) AS (SELECT 1 FROM E08 a INNER JOIN E08 b ON a.N = b.N), --10E16 or more rows than you'll EVER need
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) * SIGN(@End - @Start) FROM E16)
--===== Final SELECT uses a TOP "reflection" back on the CTE's to limit rows
-- calculated by the CTE's to only those needed.
SELECT TOP (ABS(@End - @Start) + 1)
N + @Start + SIGN(@Start - @End) AS N
FROM cteTally

It doesn't slow the code down at all and all but the most dedicated of DBA's won't see that the inner join on all 1's in the various CTE's as actually being a CROSS JOIN. I don't use this method because 1) most DBA's don't look close enough to figure out the original code is all CROSS JOINs, 2) it makes the code longer (and uglier IMHO) and 3) I have to keep my skill up in launching pork chops.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #839083
Posted Thursday, December 24, 2009 3:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
Nice work Jeff



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #839085
Posted Thursday, December 24, 2009 4:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:25 AM
Points: 1,262, Visits: 3,419
Jeff Moden (12/24/2009)
Dugi, thanks for the kudo but it's not mine alone... take a look at the "credits" on that thing... lots of folks had some great ideas that improved the great idea that Itzik originally came up with.

Oh... and you may actually have a "bad" copy if you copied the code earlier, Dugi. Much to my mortification, I originally posted an old copy that still had the "word" Cross-Join in one of the comments that the "stone wall" called me out on. I put the correct copy up now.

As a side bar, I really need to spend some time organizing my code snippets.


I'm talking about this thread and talking about just inside the problem where you post the best solution that you can do! I know also the other that are in your same level of T-SQL Mastering and I appreciate your help!



============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #839098
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse