Jeff Moden

Forum Replies Created

Viewing 15 posts - 44,236 through 44,250 (of 59,101 total)

RE: How to make this stored procedure flexible?

Heh... ok, Paul. From the way you were talking, I thought you were on to an improvement.
--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)

April 12, 2009 at 7:35 pm

#976360
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Thanks for your concern. If I'm not mistaken, PIVOT can do multiple aggregations... I just gotta look back in my notes because I don't use PIVOT for all the...
--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)

April 12, 2009 at 7:25 pm

#976359
RE: T-SQL 2005 Help

All of that may be true... I just hope they never remove the capability because it is an option. It's kind of like cursors... I generally deplore their usage...
--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)

April 12, 2009 at 7:52 am

#976281
RE: How to make this stored procedure flexible?

Paul White (4/12/2009)
Jeff Moden (4/11/2009)
You may want to reconsider ever using a Pivot operator... a properly formed crosstab can be nearly twice as fast...
http://www.sqlservercentral.com/articles/T-SQL/63681/
Performance meaurements are at the bottom 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)

April 12, 2009 at 7:39 am

#976279
RE: Which is more efficient or does it matter?

Paul White (4/11/2009)
Jeff Moden (4/11/2009)
...A persisted computed indexed column would do very nicely here.
Hey Jeff,
It would, but an ordinary column would be my first choice, mainly because persisted computed columns...
--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)

April 12, 2009 at 7:36 am

#976278
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Outstanding... thanks for the post and the code, Paul... I'll check it out. That's what I love about forums. Lots of folks with lots of ideas.
--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)

April 12, 2009 at 7:31 am

#976277
RE: TSQL help needed

Bob Hovious (4/11/2009)
See if these are quick enough for you. The use of FOR XML is the fastest concatenation technique I've come across, but this sort of string...
--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)

April 11, 2009 at 9:27 pm

#976237
RE: Creating Index on Table which containing Millions of rows

I agree... it would probably be better to not move the data before processing. And, if that's still somehow necessary, I've had great success with just dropping the table...
--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)

April 11, 2009 at 8:15 pm

#976234
RE: Which is more efficient or does it matter?

Paul White (4/11/2009)
2. If the first 3 characters of the zip are to be used in this way on more than an ad-hoc basis, it might be sensible 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)

April 11, 2009 at 8:07 pm

#976232
RE: how to increment id value in store procedure if id is primary key

To add to what Flo stated... if you insert more than one row with an IDENTITY column, you can use OUTPUT to capture all of the IDENTITIES created as 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)

April 11, 2009 at 3:40 pm

#976210
RE: How to transfer rows data to dynamic column

A well formed crosstab can be almost twice as fast as a Pivot and a whole lot easier to read, to boot. Please see the following articles...
http://www.sqlservercentral.com/articles/T-SQL/63681/ (Performance...
--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)

April 11, 2009 at 3:38 pm

#976209
RE: heap or clustered

I think you could probably cut way down on the scans and rowcounts if you did a little "Divide'n'Conquer" to do some pre-aggregations to temp tables and join on those.
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)

April 11, 2009 at 3:25 pm

#976206
RE: Which is more efficient or does it matter?

Bob Hovious (4/11/2009)
Hey... who are you callin' an oxymoron!? :unsure:
Heh... so you know that guy, too, huh? 😀
--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)

April 11, 2009 at 3:16 pm

#976202
RE: Are the posted questions getting worse?

Heh... no... that's certainly not ring knocking in my book. It takes a certain amount of hair to stand up in front of a worldwide community and speak. ...
--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)

April 11, 2009 at 3:09 pm

#976198
RE: How to make this stored procedure flexible?

You bet... thanks for the feedback, Flo.
--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)

April 11, 2009 at 3:04 pm

#976196

Viewing 15 posts - 44,236 through 44,250 (of 59,101 total)