InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:09:29 GMT20http://www.sqlservercentral.com/Forums/Topic838876-145-1.aspx[quote][b]RBarryYoung (12/25/2009)[/b][hr]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:[/quote]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. :-PFri, 25 Dec 2009 10:32:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr]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...[code="sql"] 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[/code]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[/quote]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:Fri, 25 Dec 2009 02:00:36 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspx[quote][b]Dugi (12/24/2009)[/b][hr][quote][b]Jeff Moden (12/24/2009)[/b][hr]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:[/quote]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![/quote]Heh... I stand corrected. Thanks for the compliment, Dugi. Always appreciated.Jason, thank you as well.Thu, 24 Dec 2009 16:56:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr]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:[/quote]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!Thu, 24 Dec 2009 16:37:54 GMTDugihttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxNice work JeffThu, 24 Dec 2009 15:29:43 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxHeh... 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...[code="sql"] 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[/code]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. :-PThu, 24 Dec 2009 15:26:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxDugi, 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:Thu, 24 Dec 2009 15:14:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxHeh... 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 [i]read [/i]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. :-PThu, 24 Dec 2009 15:02:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxJeff 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;[i]Jeff Moden[/i] - sorry ah!Thu, 24 Dec 2009 14:55:15 GMTDugihttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxNabha 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...[code="sql"] 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 )[/code]Once you have such a function built, then problems like yours become child's play...[quote] have a range of values 12000 - 13000In sql I would like to reproduce the values between the two figures i.e.12000,12001,12002 etc up to 13000 and display them.[/quote][code="sql"]DECLARE @Start INT, @End INT; SELECT @Start = 12000, @End = 13000; SELECT N FROM dbo.TallyRange(@Start,@End)[/code]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. :-)Thu, 24 Dec 2009 14:46:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxIf the difference between start and end is within the limit of 2047, then this is 'one' of the method, [code="sql"]Declare @start intSET @start = 12000Declare @End intSET @end = 13000SELECT @start + t.number FROM Master.dbo.spt_Values t WHERE t.Type = 'P' AND t.Number BETWEEN 0 AND (@end - @start)[/code]You should read this link by the way, (very useful)[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]Thu, 24 Dec 2009 01:06:13 GMTNabhahttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxFirst of all your operator here is wrong "=>" it must be >= or <=So you declared the parameter but you didn't use it!Thu, 24 Dec 2009 00:42:10 GMTDugihttp://www.sqlservercentral.com/Forums/Topic838876-145-1.aspxHello,I have a range of values 12000 - 13000In 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 thenbatch + 1;else0Thu, 24 Dec 2009 00:25:13 GMTmartin.edward