Jeff Moden

Forum Replies Created

Viewing 15 posts - 46,831 through 46,845 (of 59,095 total)

RE: Recommended path for advanced training?

I solved a post, last night, using the SIGN function instead of CASE for a pretty good performance gain. It reminded me of what my original programming mentor taught...
--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)

December 3, 2008 at 3:59 pm

#907228
RE: Help with Data transformation

ishaan99 (12/3/2008)
Thanks Guys i used the charindex without sign function to resolve this. Thanks for everybody;s input.
You bet... thanks for the feedback.
--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)

December 3, 2008 at 3:50 pm

#907221
RE: Help with Data transformation

Bob Hovious (12/3/2008)
... and now I know there is a SIGN function... which of course returns just exactly what is needed for setting the "bits". Remind me never...
--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)

December 3, 2008 at 3:49 pm

#907219
RE: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

mlandry (12/3/2008)
Jeff,
Whoa, using SQL to do cross-tabs really is "old school" (circa mid-90's or before.) It just wasn't designed for this.
Do yourself a BIG favor and try Analysis Services....
--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)

December 3, 2008 at 9:41 am

#906997
RE: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

ggraber (12/3/2008)
Great article Jeff!
Do you think this will perform better than a dynamic pivot?
Thanks for the feedback and absolutely! 🙂
--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)

December 3, 2008 at 8:25 am

#906914
RE: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Thanks for the great feedback Anirban. Great to know that folks get things out of these articles. 🙂
--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)

December 3, 2008 at 8:00 am

#906895
RE: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Chirag (12/3/2008)
Another masterpiece.
Small typo -
You have a single quote missing at the end of the date for the code under OLD fashioned swap.
"SET @EndDate = '2008-01-15 "
Thanks...
--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)

December 3, 2008 at 5:03 am

#906757
RE: Script to find the datafile size and logfile size?

Is this what you want?
--===== Declare local files
--Jeff Moden
DECLARE @DBID INT
DECLARE @DBName VARCHAR(30)
DECLARE @Text ...
--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)

December 2, 2008 at 10:57 pm

#906655
RE: Convert Rows to columns so...

fausto usme (12/2/2008)
Hello People,
i need to do the next.
Why? What's the goal or business rule other than someone said they wanted it?
--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)

December 2, 2008 at 10:52 pm

#906653
RE: Processing a non-locked row update from within a TRANSACTION

John Barry (12/2/2008)
Jeff,
Thanks - I have used that syntax as well - it's cool. And I agree if I move the Nextval outside the transaction it would solve 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)

December 2, 2008 at 10:46 pm

#906651
RE: UPDATE SET =NULL running over an hour

One more thing... you're updating the same columns that you have in your criteria. Sometimes, and it's again unpredictable, that will cause a recompile for every row updated. ...
--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)

December 2, 2008 at 10:42 pm

#906650
RE: UPDATE SET =NULL running over an hour

P.S. You may want to stop that single Update you're running right now. It may take several hours to rollback or it might rollback quicker. But even...
--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)

December 2, 2008 at 10:38 pm

#906649
RE: UPDATE SET =NULL running over an hour

To add to what Lynn stated.... there are two reasons you need to break up such a large update... the first is the LOG file. Even with the SIMPLE...
--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)

December 2, 2008 at 10:36 pm

#906648
RE: How to convert hhmm into datetime 13:30:00 T-SQL?

That's not really a DateTime datatype you asked for or it would have a date with it like this...
DECLARE @Time CHAR(4)
SET @Time = '1330'
SELECT CAST(STUFF(@Time,3,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)

December 2, 2008 at 7:40 pm

#906613
RE: Processing a non-locked row update from within a TRANSACTION

John Barry (12/2/2008)
I do not want to use an identity field in the tables to get a sequence number. This would present many additional challenges that are worse than...
--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)

December 2, 2008 at 7:27 pm

#906609

Viewing 15 posts - 46,831 through 46,845 (of 59,095 total)