Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,701 through 50,715 (of 59,091 total)

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

RyanRandall (5/7/2008)
Mark (5/7/2008)
Jeff Marcus (5/7/2008)
If you are using SQL Server 2005 you can use ROW_NUMBER instead.
If you're not using SQL 2005, I guess you could simulate ROW_NUMBER like this...
declare @param...
--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:54 pm

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

Hugo Kornelis (5/7/2008)
buktseat (5/7/2008)
This is probably a rediculous question, but how can any of your "using the tally table" examples work if you don't declare N? I guess I...
--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:33 pm

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

GSquared (5/7/2008)
Excellent article, Jeff. Good introduction.
I've been using Numbers tables for a couple of years now, and I just keep finding more uses for them all the time.
Examples include:...
--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:30 pm

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

JohnG (5/7/2008)
As always, a great article from Jeff.
As for performance testing of various techniques by anyone, I am continually amazed as to the incorrect methods for generating sample data.
Example (taken...
--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:26 pm

#812210
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

Viewing 15 posts - 50,701 through 50,715 (of 59,091 total)