Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,206 through 41,220 (of 59,102 total)

RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Lynn Pettis (11/10/2009)
I have found the "quirky update" useful when helping others who needed to capture and update intermediate results along with the final results of a calculation.
It helps break...
--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)

November 10, 2009 at 10:32 pm

#1077641
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
I then changed the options from LOCAL FORWARD_ONLY READ_ONLY to LOCAL STATIC READ_ONLY and shaved off another half a mintue - 3:58
Hi Hugo,
Heh... just some feedback so you...
--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)

November 10, 2009 at 10:03 pm

#1077635
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
I can understand that people use this method to drop from 8 minutes to less than 10 seconds. But I do hope that, now that I have presented...
--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)

November 10, 2009 at 5:42 pm

#1077604
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
The code I posted was not intended to be proof of breaking the quirky update. This particular comment was only posted to show that the presence of 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)

November 10, 2009 at 5:29 pm

#1077601
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
Looks like I should write up another blog post demonstrating how to add multiple rows at a time...
That would be very cool especially if it used neither RBAR...
--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)

November 10, 2009 at 4:02 pm

#1077584
RE: Using OPENROWSET to import CSV files

Jeff Moden (11/6/2009)
Simon Parry (11/6/2009)
Hi
i have tried using BULK INSERT
using
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
exec (@string)
but this requires the destintation table 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)

November 10, 2009 at 3:53 pm

#1077581
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

You have to observe all the rules at once, Hugo. I said in the rules that you can't use a JOIN in conjunction with the "Quirky Update" and 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)

November 10, 2009 at 3:49 pm

#1077579
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
Jeff,
In my experience, if we need running totals, then we have some unique order of rows to calculate those totals with respect to. Because we know the running...
--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)

November 10, 2009 at 3:02 pm

#1077556
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
... and shaved off another half a mintue - 3:58.
Heh... very cool optimization of the cursor, Hugo. You've managed to upgrade it to only being 33...
--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)

November 10, 2009 at 2:59 pm

#1077554
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
Jeff, I am not sure how RBAR is relevant to denormalization, yet when the running totals are stored right in your row, you don't need any joins, 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)

November 10, 2009 at 1:31 pm

#1077484
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

talltop-969015 (11/10/2009)
Alexander Kuznetsov-291390 (11/10/2009)
I decided that I don't want to put it into production - they are just a little bit too complex and they do not look robust 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)

November 10, 2009 at 9:33 am

#1077356
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
These are incredibly smart techniques, but after playing with such approaches I decided that I don't want to put it into production - they are just a little...
--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)

November 10, 2009 at 9:22 am

#1077345
RE: Date conversion into AM/PM Format

suresh.theyyath (11/10/2009)
Hope this will works for you!
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/
Heh... I wrote such a function earlier in my SQL career... it's a fun function to write because it creates a huge understanding of...
--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)

November 10, 2009 at 7:23 am

#1077234
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
Hi Jeff,
Thanks for doing a rewrite of this article.
Just a few comments before I start really reading the article:
1) Being one of the people who tried to grill...
--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)

November 10, 2009 at 6:54 am

#1077200
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

ta.bu.shi.da.yu (11/10/2009)
That's an awesome article Jeff!
Now if only I could work out how to run a query that traverses a directed graph with cycles - but where there 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)

November 10, 2009 at 6:46 am

#1077192

Viewing 15 posts - 41,206 through 41,220 (of 59,102 total)