Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,821 through 41,835 (of 59,067 total)

RE: SQL Table data for annual memberships

"Slowly Changing Dimension Type 2". I couldn't give you a better explanation that what's already on Wikipedia. Take a look.
--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)

September 22, 2009 at 5:04 pm

#1057194
RE: The Dynamic Tally or Numbers Table

I'll have to test it tomorrow. I'm on a different machine for the night.
--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)

September 22, 2009 at 5:00 pm

#1057193
RE: The Dynamic Tally or Numbers Table

Just to keep from doing the apples'n'oranges thing, I ran the new code with both changes. The optimization came pretty close to being negated by the extra checking. ...
--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)

September 22, 2009 at 3:34 pm

#1057173
RE: The Dynamic Tally or Numbers Table

Hmmmm.... hadn't thought about that. A note about the discussions would probably be appropriate here.
--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)

September 22, 2009 at 2:52 pm

#1057149
RE: The Dynamic Tally or Numbers Table

BWAA-HAA!!! Correct... guess it really matters how "angry" the DBA really is. 🙂
--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)

September 22, 2009 at 2:19 pm

#1057128
RE: The Dynamic Tally or Numbers Table

Lynn Pettis (9/22/2009)
First, leave it to you squeeze another 33% out of a routine, awesome. I'll have to incorporate that change into my code. I think Steve needs...
--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)

September 22, 2009 at 2:04 pm

#1057120
RE: The Dynamic Tally or Numbers Table

the sqlist (9/22/2009)
Jeff Moden (9/22/2009)
I'm using SQL SERVER Profiler with the data produced by the queries routed to a temp table. Routing the output to the screen gives false...
--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)

September 22, 2009 at 2:01 pm

#1057117
RE: temp db is logspace used is >90%.

I;m still waiting on what the current settings of TempDB actually are for this problem so that we can figure out what "90%" actually means. 😉
--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)

September 22, 2009 at 1:57 pm

#1057113
RE: The Dynamic Tally or Numbers Table

the sqlist (9/22/2009)
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:
declare @i bigint
set @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)

September 22, 2009 at 1:45 pm

#1057103
RE: The Dynamic Tally or Numbers Table

the sqlist (9/22/2009)
Jeff Moden (9/22/2009)
Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂
The other problem with that method is performance. It takes more than 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)

September 22, 2009 at 1:43 pm

#1057099
RE: The Dynamic Tally or Numbers Table

Lynn Pettis (9/22/2009)
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:
declare @i bigint
set @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)

September 22, 2009 at 1:28 pm

#1057088
RE: The Dynamic Tally or Numbers Table

the sqlist (9/22/2009)
Nice article but it has a problem: the function CAN'T be created (or it's code used) on SQL server 2000 because of the CTE approach and and row_number()...
--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)

September 22, 2009 at 1:26 pm

#1057084
RE: The Dynamic Tally or Numbers Table

laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:
declare @i bigint
set @i = 0
select @i=@i+1...
--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)

September 22, 2009 at 1:17 pm

#1057077
RE: The Dynamic Tally or Numbers Table

timothyawiseman (9/22/2009)
Great article Lynn. It seems that even if you aren't permitted to change the schema that if you are going to use it multiple times you might get...
--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)

September 22, 2009 at 11:25 am

#1056990
RE: The Dynamic Tally or Numbers Table

Lynn Pettis (9/22/2009)
Also remember that syscolumns is depreciated. It is there for backward compatibility.
Agreed. For existing 2k and 2k5 apps that aren't likely to migrate, that's probably ok...
--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)

September 22, 2009 at 11:13 am

#1056974

Viewing 15 posts - 41,821 through 41,835 (of 59,067 total)