Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,841 through 51,855 (of 59,098 total)

RE: Combining Columns into one output field?

... or, you can just use the power of UNION...
SELECT customer1
from yourtable
where customer1 like 'sm%' UNION
SELECT customer2
from yourtable
where customer2 like 'sm%' UNION
SELECT customer3
from yourtable
where customer3 like 'sm%'
Real key...
--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)

March 13, 2008 at 8:05 am

#789067
RE: Batch Cost & Actual Time

Lynn Pettis (3/13/2008)
Again, the cost is relative to the batch its self, and it is quite possible that the cost of the query (relative to the batch) is higher.
I've found...
--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)

March 13, 2008 at 8:01 am

#789057
RE: Can a temporary table created with an execute statement survive that statement?

Matt Miller (3/12/2008)
Jeff Moden (3/10/2008)
Giving away a fine "secret" here, but no one's ever heard of temporary stored procedures? Build it using dynamic SQL... execute it... it's all in...
--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)

March 13, 2008 at 7:58 am

#789052
RE: Can a temporary table created with an execute statement survive that statement?

Yes... same problem as with Global Temp Tables... if same job runs more than once, BOOM on table creation or YECH on what happens to the data because more 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)

March 13, 2008 at 7:54 am

#789046
RE: Renaming all tables in a Database

Do a Google search for sp_MSForEachTable...
--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)

March 13, 2008 at 7:39 am

#789032
RE: Linking to the Previous Row

Jack Corbett (3/13/2008)
Nice article. I like the fact that is clearly takes you from start to finish and offers a solution to a commonly encountered problem.
Agreed... and test 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)

March 13, 2008 at 7:35 am

#789029
RE: Batch Cost & Actual Time

I have sp2 installed and I noticed the same thing an a good number of queries that end up using a loop join...
I've never trusted % if batch, anyway......
--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)

March 13, 2008 at 7:31 am

#789026
RE: Return value in stored procedure

Wouldn't OUTPUT return the same data type as whatever column or variable appeared in the stored procedure?
--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)

March 13, 2008 at 7:25 am

#789023
RE: Performance tuning

While it's true that there is some trickery you can perform to "Tune the hardware" and "Tune the database" and you can throw indexes at tables to make some queries...
--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)

March 13, 2008 at 7:12 am

#789011
RE: create temp view

No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast 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)

March 13, 2008 at 7:00 am

#789000
RE: SELECT certain number of rows

Heh... LOL. I guess using TOP to overcome a double triangular join will do. 😀 I'll add it to my list of paging methods to test for...
--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)

March 13, 2008 at 6:43 am

#788985
RE: SELECT certain number of rows

Grant Fritchey (3/12/2008)
It sounds like a paging problem.
One, of many, solutions that Itzik Ben-Gan offers in TSQL Querying:
I wish great authors would warn people of the code the create. ...
--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)

March 12, 2008 at 5:35 pm

#788831
RE: Performance Question

I built a million row table as you described. I made it so that the Status column has only 5 different possiblities and verified the distribution as an average...
--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)

March 12, 2008 at 5:24 pm

#788828
RE: Can a temporary table created with an execute statement survive that statement?

Adding columns to a temp table or any table that uses ALTER is always a PITA... during run time, the new columns are perceived as NOT THERE. That means...
--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)

March 12, 2008 at 3:03 pm

#788811
RE: Findstring question

It simply substracts 2 from the character position returned by the FINDSTRING.
--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)

March 12, 2008 at 2:34 pm

#788797

Viewing 15 posts - 51,841 through 51,855 (of 59,098 total)