Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,376 through 6,390 (of 59,072 total)

Reply To: Number weeks from April to September for every year

Johan Bijnens wrote:
Jeff published a wonderfull article called: Create a Tally Function
Thank you for the kudo. If you've not subscribed to the article discussion, you may want to. 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)

September 21, 2020 at 6:44 pm

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

Steve Collins wrote:
Jeff, I was going to mark your response above as the answer but it actually doesn't answer the question. Your answer is merely complete and I asked for minimal. ...
--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 21, 2020 at 5:34 pm

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

Steve Collins wrote:
Jeff Moden wrote:
Ah... almost forgot... What to do about the folks that claim that performance doesn't matter if you know that here will always only be a small number 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 20, 2020 at 7:06 pm

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

Steve Collins wrote:
That person's query also contained a RIGHT OUTER JOIN. When was the last time you actually saw one of those not in a textbook?
About 10 minutes ago. I use...
--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 20, 2020 at 6:55 pm

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

Mr. Brian Gale wrote:
Some things CAN be done in SQL, but can be done so much faster and/or easier in other languages.
While that is certainly true, I find that it's a matter...
--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 20, 2020 at 3:16 am

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

Steve Collins wrote:
Ok here are two recent winning recursive cte's. Be gentle when evaluating the opposing answers 🙂
https://stackoverflow.com/questions/63922869/get-an-interval-of-dates-from-a-range-of-dates/63923536
https://stackoverflow.com/questions/63919706/fill-missing-months-on-a-date-query/63920844
As a bonus, here losing while using an ordinal splitter
https://stackoverflow.com/questions/63886788/extracting-date-from-text-in-sql-using-cursor/63887736
Thank you for posting those...
--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 20, 2020 at 12:19 am

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

Ah... almost forgot... What to do about the folks that claim that performance doesn't matter if you know that there will always only be a small number of rows involved...
--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 9:46 pm

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

Ok, here we go...
The code that you originally wrote generates the full range of values that the inline cascading CTE for the Tally cte can generate and it does so...
--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 8:07 pm

#3789665
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

Viewing 15 posts - 6,376 through 6,390 (of 59,072 total)