Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,406 through 6,420 (of 59,089 total)

Reply To: How to create a minimal tally table and to describe its advantages (if any)

Steve Collins wrote:
What is the minimal way to create a tally table/tvf/cte and to describe its advantages (if any) versus recursive cte's? For the past several weeks I've observed most (all)...
--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 19, 2020 at 3:39 pm

#3789646
Reply To: How to create a minimal tally table and to describe its advantages (if any)

Steve Collins wrote:
Jeffrey Williams, the more I look at your code the more I like it. I definitely missed a few things and using TOP in the select is so obvious...
--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 19, 2020 at 3:21 pm

#3789641
Reply To: How to create a minimal tally table and to describe its advantages (if any)

Steve Collins wrote:
However, analysis of the execution plan shows it's horribly inefficient.
Until the later versions of SSMS 18.X, the execution plan doesn't really have anything that is actually a good determination...
--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 19, 2020 at 3:13 pm

#3789639
Reply To: How to create a minimal tally table and to describe its advantages (if any)

Steve Collins wrote:
Over and over and over the tally based solutions almost always lose to a recursive cte based solution.
I've not checked the other posts on this thread yet but 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)

September 19, 2020 at 3:08 pm

#3789638
Reply To: I need all jobs, job steps and schedule for a particular database

Johan,
If I quote your post, I can see your script in it but it's not doing a normal show on your post.
--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 18, 2020 at 12:40 pm

#3789390
Reply To: SQL Alert for stack dumps

Admingod wrote:
If I have more than 50 servers do i need to create the Job in all 50 servers?
No. Look for Get-DBADump at the following link. It's part of 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 18, 2020 at 12:10 am

#3789295
Reply To: Solving Real Problems

From the article...
Extended Events, just for example, is wildly superior to Profiler/Trace.
Heh... NOT! 😀 But we'll save that discussion for another time.
I agree with a whole lot that you've...
--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 17, 2020 at 11:47 pm

#3789291
Reply To: when to use Python in SQL Server

My comment would be to not use Python to duplicate what can be done in SQL Server. Only use it if you have something that SQL Server can't do 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)

September 17, 2020 at 11:38 pm

#3789290
Reply To: Insert vs Update

Since the tables are being truncated and then repopulated by inserts, they'll be pretty well packed at the page level of nearly 100% page fullness before they get hit 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)

September 17, 2020 at 11:10 pm

#3789284
Reply To: My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping

mjvisintin wrote:
Okay,
Thanks,
I thought it was just growing because we are putting so much data in there and that involves a transaction.
So you are indicating that they would be committed 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)

September 17, 2020 at 5:23 pm

#3789220
Reply To: My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping

mjvisintin wrote:
My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping by throwing it out of synch.
No matter when I try to time it, it throws of...
--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 17, 2020 at 4:29 pm

#3789213
Reply To: SQL 2014 Table Truncated Inadvertently

I see nothing wrong with such a DDL trigger to detect when a new database was created... but I wouldn't rely on it to make sure backups are performed. 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 17, 2020 at 4:24 pm

#3789212
Reply To: How to find size of each filegroup?

coolchaitu wrote:
How to find size of each filegroup?
At least try to help yourself...
https://www.google.com/search?q=How+to+find+size+of+each+filegroup%3F
--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 17, 2020 at 4:13 pm

#3789210
Reply To: Insert vs Update

Without seeing the actual execution plans of the parts that are taking the longest, we're all just "guess'n and suggest'n". You need to figure out what the "high points" are...
--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 17, 2020 at 4:08 pm

#3789209
Reply To: I need all jobs, job steps and schedule for a particular database

p.s. I also agree with Phil... if you have SSIS, SSAS, and other SQL "4 letter words" involved in jobs, this could end up being a fair bit more difficult...
--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 17, 2020 at 3:36 pm

#3789194

Viewing 15 posts - 6,406 through 6,420 (of 59,089 total)