Jeff Moden

Forum Replies Created

Viewing 15 posts - 58,546 through 58,560 (of 59,067 total)

RE: Dynamic SQL for more than 4000 Bytes

Don't use sp_Execute. Just use EXEC...
EXEC (@SQL1+@SQL2+@SQL3....+@SQLn)
... and each of the variables can be VARCHAR(8000).
--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 17, 2005 at 10:13 pm

#610219
RE: Loop through each record and then each field within each record

dba321,
The Inserts that Phil provided are just test code to simulate your much larger table (or result set, whatever)! You don't need any of those Inserts and you certainly don't...
--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 17, 2005 at 10:04 pm

#610218
RE: Seconds to dd:hh:mm:ss

You certainly don't need a cursor and, if you don't want, you don't need a function... "Trust the force, Luke."
SELECT CONVERT(VARCHAR(10),SecondsCol/86400)+':'
+ CONVERT(VARCHAR(8),DATEADD(ss,SecondsCol,0),108)
FROM yourtable
The forces 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)

December 14, 2005 at 11:08 pm

#609642
RE: Finding skipped values

I don't think he meant to be sarcastic... he was just having fun and beat most of us to it. It's hard to tell in email when someone is joking...
--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 13, 2005 at 9:18 pm

#609391
RE: spaces in id feild

It's customary to share your final solution, if you don't mind.
--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 12, 2005 at 7:05 pm

#609126
RE: Julian to calendar

Jon,
David is correct... since Julian dates recycle every 10 years, should we assume that you will never have a future dated Julian date?
Also, would you rather (recommended) have the 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)

December 12, 2005 at 7:02 pm

#609125
RE: How to pivot the table in SQL Server?

David is correct... his link leads to some code I wrote for him to make a "dynamic" cross-tab. Might have a fat-finger here and there but David wrote and posted...
--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 12, 2005 at 6:05 pm

#609120
RE: How to pivot the table in SQL Server?

Jennifer,
Since you want this done in OLTP instead of OLAP, these are more commonly known as "crosstabs" and they run pretty darned fast especially when compared to a cursor and...
--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 11, 2005 at 9:57 pm

#608937
RE: Finding skipped values

With all the good solutions, I feel a little silly posting this but it's nasty fast... and, since a lot of folks keep their Tally (numbers) tables down to something...
--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 11, 2005 at 2:01 pm

#608914
RE: Best way to stamp records with who created / modified and when?

Defaults don't work on rows that have already been inserted. Leaving it up to Developers to remember to include it in code is OK provided the code has to go...
--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 10, 2005 at 10:13 pm

#608894
RE: CrossTab ''''like'''' Query

Thanks for the nice things you said, David. Surprisingly enough, I don't believe you'll find a huge performance gap just because of the use of the Dynamic SQL. Dynamic SQL...
--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 10, 2005 at 10:18 am

#608878
RE: User Defined Functions Investigation

BWAAAA-HAAAAA-HAAAAAA-HAAAAA! I just ran the same set of tests on a 4 processor box "real server" with minor traffic for 2,000,000 records using my previous test code and the differences...
--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 10, 2005 at 10:09 am

#608877
RE: User Defined Functions Investigation

Ok, so does that short-term bottleneck stuff explain why my latest test consistently showed that the UDF code was twice as slow as the clear code regardless of the 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)

December 9, 2005 at 5:25 pm

#608850
RE: Reformat datetime as: mm/dd/yyyy

Vladan and Sushila are correct... you may also just try finding the first day of the next month and the looking for anything less than that.
--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 9, 2005 at 5:21 pm

#608849
RE: CrossTab ''''like'''' Query

Just keep in mind, David... it breaks at 8k... I think that won't happen for quite some time, though.
Manchester UK, eh? I've always wanted to see that place... I may...
--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 9, 2005 at 5:10 pm

#608848

Viewing 15 posts - 58,546 through 58,560 (of 59,067 total)