Jeff Moden

Forum Replies Created

Viewing 15 posts - 18,946 through 18,960 (of 59,098 total)

RE: What is difference between these 2 SP

Here's one way that will help with both parameter sniffing and a way to get SQL Server to usually pick the correct execution plan.
CREATE PROCEDURE dbo.USP_1 @Par1 NVARCHAR(10)
...
--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 20, 2015 at 8:15 am

#1846978
RE: What is difference between these 2 SP

MotivateMan1394 (12/20/2015)
Thank you Jason
And what about 'Parameter sniffing', some one say me about that ? what is it ?
Worse than parameter sniffing and much more importantly, the second method is...
--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 20, 2015 at 7:33 am

#1846973
RE: How to do IN statement as a parameter

Eirikur Eiriksson (12/20/2015)
Piling on, while the string splitting method works fine when working with small number of values the performance degrades very quickly when the number of values increases.
It's uncharacteristic...
--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 20, 2015 at 7:29 am

#1846972
RE: 2 Tables 1 recursive

Borrowing heavily from Jason's fine post and making a slight change to how the data is constructed so that people still using 2005 can easily play along if they need...
--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 19, 2015 at 9:44 am

#1846932
RE: Another Recursive Hierarchy bit of help needed...

Adi Cohn-120898 (12/8/2015)
Might not be the answer that you are looking for, but I do think that this might help you. I'm using the procedure sp_blocked_process_report_viewer that was written...
--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 19, 2015 at 8:25 am

#1846931
RE: Another Recursive Hierarchy bit of help needed...

Jason A. Long (12/17/2015)
Recursive CTEs aren't bad once you get your head wrapped around them... If you want to post some consumable test data, I'll attempt a solution.
See thee first...
--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 18, 2015 at 6:35 pm

#1846904
RE: Outer Joins

Despite it's occasional problems (mostly caused by humans), I loved the old syntax... it was great for job security especially since we also used Oracle and it's exactly the opposite....
--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 18, 2015 at 6:17 pm

#1846903
RE: Calculate Running Total until sum reach input value

vijaykumar587 (12/18/2015)
Thank you very much. It is working as I expected.
But If have lot of records, for example in my data-set I have 500 thousand records. The query is taking...
--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 18, 2015 at 6:13 pm

#1846902
RE: sql query to set default value based on a conditio

hlsc1983 (12/18/2015)
I have a table employees with some columns.
I want to set the default value of Commission column to 1000 for those records whose value in Job column is Manager....
--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 18, 2015 at 4:53 pm

#1846901
RE: Is the sa account out of fashion?

Ed Wagner (12/18/2015)
feersum_endjinn (12/18/2015)
So to clarify - advise is to disable built-in "sa" account, setup a replacement for it, call it something not so obvious. I take it that 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)

December 18, 2015 at 4:49 pm

#1846900
RE: Number of Databases per Instance

I'll throw my hat into the ring and say "It Depends".
While SQL Server can certainly handle a whole lot of databases, there may be logical or even strategic reasons 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 18, 2015 at 4:41 pm

#1846899
RE: SQL Agent Job - mail body is blank

Consider this and the symptoms...
If the email job is currently running, will any of the data that you've selected come back as NULL? ANY concatenation of NULL will wipe...
--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 18, 2015 at 4:32 pm

#1846897
RE: How to dynamically create 12 rows per customer in a SQL Table

ciskypete (12/18/2015)
Thanks for the reply Jeff! Would you possibly be able to explain this concept a little deeper? (as in, how I would go about setting this up, or at...
--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 18, 2015 at 4:23 pm

#1846896
RE: Check given number

John Hanrahan (12/18/2015)
Jeff,
A business case for this might be inventory. In our case we had a clerk enter in an invalid unit of measure then she completed some transactions....
--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 18, 2015 at 3:34 pm

#1846892
RE: Pivot query using dates between 2 dates

ChrisM@Work (12/17/2015)
twin.devil (12/17/2015)
Is there is any limit between two dates? as far as i know cross tab get very slow on reports if the columns list get higher.
you can also...
--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 18, 2015 at 3:09 pm

#1846888

Viewing 15 posts - 18,946 through 18,960 (of 59,098 total)