Jeff Moden

Forum Replies Created

Viewing 15 posts - 54,571 through 54,585 (of 59,098 total)

RE: Dynamic Query in SQL Server Function

Partitioned view might be a possibility...
--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)

October 18, 2007 at 4:25 pm

#743330
RE: Adding workdays

Kenneth Wilhelmsson (10/18/2007)
So, what's the deal with not having a dates table?
It will solve all your problems, no need for complex functions or anything else exotic.
The main problem: How do...
--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)

October 18, 2007 at 7:59 am

#743134
RE: how to remove numbers from strings?

the first solution didnt work. because of the Tally i didnt implement...
ps.
meanwhile the client "revised" some specs, so i do need these numbers now. but i still need to pull...
--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)

October 18, 2007 at 7:53 am

#743127
RE: CTE - Intermediate Results - Yes or No

Absolutely spot on, Grant!
--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)

October 18, 2007 at 7:49 am

#743122
RE: Actual Reason to Use CLR

... and, we have to help "application developers" learn how to program in T-SQL 😉 Although CLR's are helpful for those that don't really know T-SQL or set based...
--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)

October 18, 2007 at 7:46 am

#743121
RE: Thousands of "sp:starting" events for one SELECT in a proc

PS... can you be a bit more specific on what you're UDF does and post the code? UDF's are inherently NOT set-based... might be able to show you 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)

October 18, 2007 at 1:19 am

#743014
RE: Thousands of "sp:starting" events for one SELECT in a proc

Yeaup... that would also be one of the problems...
Change the EXISTS to a regular join on a "derived" table or a normal table and see if things don't pick up...
--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)

October 18, 2007 at 1:17 am

#743013
RE: how to remove numbers from strings?

Max Yasnytskyy (10/18/2007)[hrAre you trying to say that to solve this sort of problem any additional table is required?
Heck no... there are dozens of slower ways to do it :D...
--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)

October 18, 2007 at 1:08 am

#743011
RE: how to remove numbers from strings?

Max Yasnytskyy (10/17/2007)
Thanks for pointing out, as i didn't realize what server version this will run on.
I haven't ever used SQL Server 7 fortunately or unfortunately but what is wrong...
--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)

October 18, 2007 at 1:05 am

#743008
RE: returning balance value on TSQL

Andras Belokosztolszki (10/17/2007)
Search for "running total" or "running balance" on these forums.
Basically to do this in SQL and calculating this in queries is very inefficient and does not scale. 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)

October 18, 2007 at 12:58 am

#743005
RE: how to remove numbers from strings?

Max Yasnytskyy (10/17/2007)
Yeah, but he is using some sort of prefilled table isn't he?
Yep... you should try it 😀 Works well on a lot of things and the guys...
--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)

October 18, 2007 at 12:20 am

#742995
RE: how to remove numbers from strings?

mrpolecat (10/17/2007)
Tally is a table that you need to create with the column N. The fill it with the numbers 1 to whatever you think you need. This...
--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)

October 17, 2007 at 11:46 pm

#742987
RE: how to remove numbers from strings?

Max Yasnytskyy (10/17/2007)
I have found an interesintg function in msdb, seems like it is working much better ...
Ummm... this is an SQL Server 7/2000 forum... wanna tell us how well...
--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)

October 17, 2007 at 11:42 pm

#742985
RE: How to rewrite a LEFT OUTER JOIN query?

Depends... index scans can be worse than a table scan because even after the index scan, it still has to get the data.
--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)

October 17, 2007 at 11:22 pm

#742983
RE: Thousands of "sp:starting" events for one SELECT in a proc

The EXISTS with the sub-query, probably a correlated one at that, needs to be replaced with a join.
--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)

October 17, 2007 at 11:19 pm

#742981

Viewing 15 posts - 54,571 through 54,585 (of 59,098 total)