Generate sequence of numbers between values

Question

Post reply

Generate sequence of numbers between values

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Aditya Daruka Old Hand Points: 339 More actions · Answer 1

It may be slower, but still quite an interesting way of playing with t-sql

Everything is not always about speed!

I am happy with tally function, great way to tackle things.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Aditya Daruka (12/3/2015)
It may be slower, but still quite an interesting way of playing with t-sql
Everything is not always about speed!
I am happy with tally function, great way to tackle things.

Until the users complain about things taking too long to run.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Lynn Pettis (12/3/2015)
Jeff Moden (12/3/2015)
himanshu.sinha (12/1/2015)
Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .
A better way would be to avoid the loop.
Jeff, just one change I would make for code running on SQL Server 2008 and newer (yes, I realize people still need to be able support SQL Server 2005):
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(N)) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;

I never made the change to VALUES because 1) of the very reason you mention and 2) there is no perceptible performance gain. It does make for nicer looking code, though.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Aditya Daruka (12/3/2015)
It may be slower, but still quite an interesting way of playing with t-sql
Everything is not always about speed!
I am happy with tally function, great way to tackle things.

It does help when you absolutely need a loop.

Shifting gears a bit and contrary to what you and many others have stated, everything [font="Arial Black"]IS [/font]always about speed! Yes, correctness of the code is always first but performance always runs a very, very close second. And it usually takes less time to write efficient code.

Every new company that I've gone to has always had performance problems that they need to fix, especially with their front end code. The trouble is that they think the 50ms here and 100ms there isn't going to matter... until they call the code hundreds of such pieces of code thousands of times per hour. The really bad part is that ALL of the code needs to be fixed because they've died the dead of a thousand cuts and there's no single high ROI magic bullet to fix it.

Write it down and get used to it... performance ALWAYS matters.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Jeff Moden (12/3/2015)
Aditya Daruka (12/3/2015)
It may be slower, but still quite an interesting way of playing with t-sql
Everything is not always about speed!
I am happy with tally function, great way to tackle things.
It does help when you absolutely need a loop.
Shifting gears a bit and contrary to what you and many others have stated, everything [font="Arial Black"]IS [/font]always about speed! Yes, correctness of the code is always first but performance always runs a very, very close second. And it usually takes less time to write efficient code.
Every new company that I've gone to has always had performance problems that they need to fix, especially with their front end code. The trouble is that they think the 50ms here and 100ms there isn't going to matter... until they call the code hundreds of such pieces of code thousands of times per hour. The really bad part is that ALL of the code needs to be fixed because they've died the dead of a thousand cuts and there's no single high ROI magic bullet to fix it.
Write it down and get used to it... performance ALWAYS matters.

WELL SAID JEFF!!

Scalability too, which often goes hand-in-hand with performance. There is a nearly infinite amount of work out there for me and other high-end tuning consultants precisely because almost EVERYONE feels that they can just slap shit together and be fine. The cool thing is that SQL Server is just so DAMN GOOD that they can often get away with default everything (and there are some DISASTROUSLY bad defaults in SQL Server and programming models), bad schema, bad code, no maintenance, etc. and STILL be successful!!!

But MANY, if not most, of the companies out there reach a point where they need a certain level of scale, concurrent users, data size, SLA, etc. and then you just have to start doing some things right and stop doing things wrong to be successful or grow. I make a good living helping clients "retrofit" that into their sucky stuff! 😎 Unfortunately too many of them are burdened with so much technical debt that they can't truly get where they need or want to go without a HUGE amount of effort and risk - and sometimes they don't make it and go out of business before they can make that "big fish" client happy. I have had that happen to me twice actually because they didn't get me on board early enough, and it was very sad indeed.

P.S. Hope I get to see you Jeff at the Cleveland SQL Saturday in February!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

TheSQLGuru (12/4/2015)

P.S. Hope I get to see you Jeff at the Cleveland SQL Saturday in February!

I really like presenting at Cleveland. Hopefully, they'll pick my session to give me double incentive to get down there. Have you and, possibly, Steve show up would certainly sweeten the treat.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)