Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,576 through 7,590 (of 59,072 total)

Reply To: Optimise/ speedup query

IT researcher wrote:
The XQuery is slow while executing in SSMS for first time.I am using SQL Server 2008 R2.
Heh... I know... big surprise there, right? 😀
IT researcher wrote:
The total time taken 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 10, 2020 at 6:33 pm

#3732971
Reply To: SQL patching solution and automation

Saran wrote:
Thanks Sue.
Yes Jeff, the same I have asked our management, they want all in zero cost. We are small 3 person team.
Heh... you're also a 150+ server team. 😉
--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 10, 2020 at 6:01 pm

#3732940
Reply To: count(*) locking a table?
Grant Fritchey wrote:
Jonathan AC Roberts wrote:
It's the insert and update that are locking the table.
You can ignore these locks by using the nolock hint:
```
select count(*) 
  from mytable with...
```
--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 10, 2020 at 5:58 pm

#3732933
Reply To: Gap Analysis Using Algebra

Ok... "Basic Sets". Let's do that. I don't have time just now to write the full solution (on my way to work, which also means that it's not fully tested)...
--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 10, 2020 at 2:48 pm

#3732847
Reply To: The Triple Update

call.copse wrote:
Jeff Moden wrote:
...
Heh... I wonder how many people actually got this question right without having to run the code?
Even I knew about quirky update and I'm only a lowly developer....
--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 10, 2020 at 1:10 pm

#3732792
Reply To: Gap Analysis Using Algebra

First, thank you for the article. I think that anyone that steps up to the plate to share their knowledge is ok in my book.
Shifiting gears, I'm a bit confused...
--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 10, 2020 at 12:27 pm

#3732775
Reply To: The Triple Update

Rune Bivrin wrote:
Jeff Moden wrote:
Heh... I wonder how many people actually got this question right without having to run the code?
I've been using a related technique to emulate STRING_AGG, so it wasn'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)

March 10, 2020 at 12:10 pm

#3732771
Reply To: Generate Index only Script for Whole db

I've done many of those tests myself... if you can get Minimal Logging to come into play, the inserting into a Clustered Index is MUCH faster than inserting into 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)

March 10, 2020 at 11:54 am

#3732759
Reply To: The Triple Update

Thomas Rushton wrote:
From the post:
Not sure where this is useful or why it's there, but if anyone uses this, let us know.
If you yabingle "quirky update", you'll find examples of its...
--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 10, 2020 at 11:48 am

#3732758
Reply To: The Triple Update

Rune Bivrin wrote:
Interesting, but I'd be wary of using it. In a multi-row update, the result is pretty much undefined, unless all the rows are updated to the same value. 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)

March 10, 2020 at 11:45 am

#3732757
Reply To: Inserting large data volumes into a table as fast as possible

Scott In Sydney wrote:
@JeffModen:
There's a known issue with SET IDENTITY_INSERT ON... it forces full materialization of the data in TempDB and sorts it. ARCHIVE tables just don't need the IDENTITY PROPERTY on...
--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 10, 2020 at 2:18 am

#3732670
Reply To: Generate Index only Script for Whole db

Admingod wrote:
Thanks! Would this work for whole db rather than individual table? How about Primary keys and clustered index?
You've got to be really careful about disabling any PK or UNIQUE...
--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 10, 2020 at 12:26 am

#3732653
Reply To: Restoring file group from one database to another

No... you still have the LSN problem.
--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 10, 2020 at 12:09 am

#3732652
Reply To: SQL patching solution and automation

As a bit of a side bar, I'm always amazed when someone says they have a shedload of servers but no money to actually support them. It just doesn't make...
--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 10, 2020 at 12:07 am

#3732651
Reply To: what type of join is this?

jcelko212 32090 wrote:
>> your examples didn't have a join in - they were single table selects <<
If you've done a good job with your schema, then they will be a lot...
--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 9, 2020 at 11:25 pm

#3732639

Viewing 15 posts - 7,576 through 7,590 (of 59,072 total)