Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,716 through 50,730 (of 59,102 total)

RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

GSquared (5/7/2008)
I ran some speed and load tests on this code.
Summary: While the CTE works, and is fast by any normal standard, the Numbers version is even faster, and requires...
--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)

May 7, 2008 at 10:15 pm

#812208
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

GSquared (5/7/2008)
Hugo:
Just SP is fine (though I personally dislike the habit - as if anything other than a stored procedure might follow an EXEC keyword). It's SP_ (with an...
--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)

May 7, 2008 at 9:59 pm

#812205
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Carl Federl (5/7/2008)
From the article, you wrote " Tally table .. starting at 0 or 1 (mine start at 1)"
There are advantages for having the Tally table include 0...
--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)

May 7, 2008 at 9:55 pm

#812204
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Marius Els (5/7/2008)
:smooooth: This is slick...... Great work Jeff!
This is a great example of implementing set-based programming, three thumbs up from me.
Now to apply this to problems going forward!
Thanks for...
--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)

May 7, 2008 at 9:44 pm

#812202
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Mike DiRenzo (5/7/2008)
This is a super cool article. Let me repeat, this is a super cool article. What a refreshing read!
I look back on all the TSQL...
--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)

May 7, 2008 at 9:41 pm

#812201
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Carl Federl (5/7/2008)
Another great article ! Thanks
The first reference for a tally table that I can recall is in "Guide to Sybase and SQL Server" by C. J. Date...
--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)

May 7, 2008 at 9:37 pm

#812197
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

chris (5/7/2008)
How about using recursion :hehe: to replace loops? (One of the great new functions in SQL2005)
No need for a tally or temporary tables.
Heres a params example:
DECLARE @Params varchar(8000)
SET @Params...
--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)

May 7, 2008 at 9:27 pm

#812195
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

karthikeyan (5/7/2008)
Jeff,
Excellent Article!
It seems that you have written for me at the right time.:) Really, It is very useful for me.
Because so far i have replaced more than 5 cursors...
--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)

May 7, 2008 at 8:53 pm

#812189
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Chirag (5/7/2008)
nice article jeff.
Here's another one with more uses of tally table.
http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
Thanks for the compliment, Chirag.
Yeah, they do have some neat uses on that particular link. It's very strange...
--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)

May 7, 2008 at 8:50 pm

#812188
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Calvin Lawson (5/7/2008)
OMG, everybody out of the pool, quick! Read this article, now! Do not write a single piece of SQL code until you've finished it, and understand...
--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)

May 7, 2008 at 8:38 pm

#812185
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

RyanRandall (5/7/2008)
Nice work, Jeff. That plural table name must've been difficult for you to come to terms with 😀
Heh... it kept me up all bloody night! But "When 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.
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)

May 7, 2008 at 8:36 pm

#812184
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Shamshad Ali (5/7/2008)
Hello,
My quewtion is related to replacing loop, I am very interested on this. coz i really want to improve performance and I can't find a way to...
--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)

May 7, 2008 at 8:34 pm

#812183
RE: Running Total variable resolution

Heh... remember, you asked what could be done to make this more readable...
Here's one of your inserts including the indentation I found when I copied it... quick, you're...
--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)

May 7, 2008 at 8:06 pm

#812181
RE: Running Total variable resolution

Keith DuAime (5/7/2008)
ok, I am getting the result set I finally need but it's still pretty messy, I abbreviated all the data needed but this code will run alone. Any...
--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)

May 7, 2008 at 6:35 pm

#812171
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

You folks are absolutely the Best! I'm humbled by your awesome compliments and the testing that alot of you have taken the time to do and post.
I've got a...
--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)

May 7, 2008 at 8:07 am

#811808

Viewing 15 posts - 50,716 through 50,730 (of 59,102 total)