Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Incrementing Values Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, December 24, 2009 4:56 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
Post #839101
 Posted Friday, December 25, 2009 2:00 AM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 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 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. 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! -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #839127
 Posted Friday, December 25, 2009 10:32 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 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! 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"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." Helpful Links:How to post code problemsHow to post performance problems
Post #839149

 Permissions