SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Incrementing Values


Incrementing Values

Author
Message
martin.edward
martin.edward
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 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
Dugi
Dugi
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7168 Visits: 3511
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/
Nabha
Nabha
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2971 Visits: 1815
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/

---------------------------------------------------------------------------------
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340443 Visits: 42644
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.
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 problems
How to post performance problems
Forum FAQs
Dugi
Dugi
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7168 Visits: 3511
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/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340443 Visits: 42644
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. :-P

--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.
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 problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340443 Visits: 42644
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. Blush

--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.
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 problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340443 Visits: 42644
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. :-P

--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.
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 problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100204 Visits: 18616
Nice work Jeff



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Dugi
Dugi
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7168 Visits: 3511
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. Blush


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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search