December 24, 2009 at 12:25 am
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
December 24, 2009 at 12:42 am
First of all your operator here is wrong "=>" it must be >= or <=
So you declared the parameter but you didn't use it!
December 24, 2009 at 1:06 am
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/
---------------------------------------------------------------------------------
December 24, 2009 at 2:46 pm
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
Change is inevitable... Change for the better is not.
December 24, 2009 at 2:55 pm
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!
December 24, 2009 at 3:03 pm
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
Change is inevitable... Change for the better is not.
December 24, 2009 at 3:14 pm
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
Change is inevitable... Change for the better is not.
December 24, 2009 at 3:26 pm
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
Change is inevitable... Change for the better is not.
December 24, 2009 at 3:29 pm
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[/url]
Learn Extended Events
December 24, 2009 at 4:37 pm
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!
December 24, 2009 at 4:56 pm
Dugi (12/24/2009)
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!
Heh... I stand corrected. Thanks for the compliment, Dugi. Always appreciated.
Jason, thank you as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2009 at 2:00 am
Jeff Moden (12/24/2009)
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 ALLSELECT 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. 😛
Heh, I was wondering if your were going to mention that way around cross-joins. ... I used methods like this for months when I was learning SQL, before I learned about the "real" CROSS JOIN! :laugh:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 25, 2009 at 10:32 am
RBarryYoung (12/25/2009)
Heh, I was wondering if your were going to mention that way around cross-joins. ... I used methods like this for months when I was learning SQL, before I learned about the "real" CROSS JOIN! :laugh:
BWAA-HAA!!! Isn't it ironic that we try to teach people how to flush out and avoid accidental cross and Triangular joins and then turn right around and teach people how to use their power and how to intentionally hide them? It's a very funny world we live in. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply