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


Incrementing Values


Incrementing Values

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87610 Visits: 41118
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

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
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

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

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87610 Visits: 41118
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. :-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
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