Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,496 through 6,510 (of 59,091 total)

Reply To: Changing nvarchar to varchar

Jeff Moden wrote:
Jeffrey Williams wrote:
In contract to @JeffModen's response - it is also possible that the client(s) are sending varchar parameters and all of those queries are using implicit conversions and full...
--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)

September 3, 2020 at 8:56 pm

#3786134
Reply To: Changing nvarchar to varchar

Jeffrey Williams wrote:
In contract to @JeffModen's response - it is also possible that the client(s) are sending varchar parameters and all of those queries are using implicit conversions and full index...
--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)

September 3, 2020 at 8:55 pm

#3786133
Reply To: allocate / deallocate table

ScottPletcher wrote:
Steve Jones - SSC Editor wrote:
As Jeff noted, include TRUNCATE in here.
I did include TRUNCATE in the trans, from the start. Not exactly sure how else you want the transaction structured.
Agreed... didn't see...
--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)

September 3, 2020 at 8:51 pm

#3786132
Reply To: how to migrate certain filegroups with partitions to another server

As you're finding out, Partitioned Tables don't make such changes easy. I drank the MS "Kool-aid" a couple of years back (only 1.2 TB total, though) and I'm in 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)

September 3, 2020 at 3:56 pm

#3786046
Reply To: Database Modeling - Relationships

hmbacon wrote:
I posted a reply and it was removed as spam. Please explain how my opinion was spam.
Marcus
Artificial "Intelligence" of the automatic SPAM removal code for this forum is 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)

September 3, 2020 at 3:05 pm

#3786039
Reply To: Changing nvarchar to varchar

You have to consider something else that most people don't and that is "Implicit Conversions". You have to know how external code is referencing the table. If it's passing string...
--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)

September 3, 2020 at 3:01 pm

#3786038
Reply To: unable to take backup

Did you ping the IP address to see if the address is still online?

--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)

September 3, 2020 at 2:53 pm

#3786029
Reply To: allocate / deallocate table

Include the TRUNCATE in the transaction. That'll lock the table until a COMMIT is done.
--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)

September 2, 2020 at 7:16 pm

#3785743
Reply To: how to get Pivot result set using 3 tables

gaurav wrote:
Thank you so much for your help.
Sir, now I have added " group by ast.asset_name" and " LEFT JOIN ctePreAgg " it works fine.
Thank you, thanks a lot 😀
Good. ...
--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)

September 2, 2020 at 6:44 pm

#3785721
Reply To: how to get Pivot result set using 3 tables

p.s. The good part about this is that you reminded me that I needed to update my old Dynamic Cross Tab article. I edited the first post in the discussion...
--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)

September 2, 2020 at 2:50 pm

#3785633
Reply To: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

PLEASE READ ME!
This article is an old one and I've been remiss in not updating it but you need to be aware of something... The creation of the dynamic...
--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)

September 2, 2020 at 2:28 pm

#3785619
Reply To: how to get Pivot result set using 3 tables

Erland Sommarskog wrote:
Jeff Moden wrote:
I generally agree with Erland on this subject and pietLinden is probably correct about SSRS BUT... this is actually fairly simple to pull off in T-SQL.
Yes, for 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)

September 2, 2020 at 2:10 pm

#3785611
Reply To: how to get Pivot result set using 3 tables

Damn... I forgot the GROUP BY. Serves me right for posting so late without any test data to check with. That's no good excuse but those are the reasons.
On 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)

September 2, 2020 at 1:55 pm

#3785599
Reply To: how to get Pivot result set using 3 tables

gaurav wrote:
Please suggest where I need to modify or where I have done wrong. Thank you
gaurav wrote:
No, quantity belongs to tblLocationAssets
Hang on... I'm checking. Heh... this is what I get...
--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)

September 2, 2020 at 1:23 pm

#3785573
Reply To: how to get Pivot result set using 3 tables

Replace the code for @SQL2 and its related comment with this. You might also want to fix the location names in the future... a lot of them have trailing spaces.
--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)

September 2, 2020 at 8:04 am

#3785474

Viewing 15 posts - 6,496 through 6,510 (of 59,091 total)