Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,381 through 1,395 (of 59,082 total)

Reply To: Calculating Prime Numbers With One Query

~~I also have to admit that I have no clue what something like this is supposed to be doing in T-SQL. In another language, maybe but not in t-SQL.~~
(prime <<...
--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)

June 20, 2023 at 3:26 pm

#4215326
Reply To: Calculating Prime Numbers With One Query

~~Ok... I'm a little gobsmacked... I ran your code with zero changes and it's riddled with syntax errors and,yet, it ran without error.~~
~~What is the following supposed to do 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)

June 20, 2023 at 3:21 pm

#4215325
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
Jeff Moden wrote:
The doubled-up relational symbology isn't floating my boat on an SQL Server forum, either.
There are functions also as I'm sure you're already aware
So why are you using...
--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)

June 20, 2023 at 3:17 pm

#4215323
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
I don't know. Setting STATISTICS ON doesn't work on Fiddle
Simplified without the 4th CTE
The doubled-up relational symbology isn't floating my boat on an SQL Server forum, either.
--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)

June 20, 2023 at 2:35 pm

#4215319
Reply To: Devil's Advocate

Sounds good. You could still be affected if you copy a DATETIME2(7) into any datatype that has a lower resolution.
--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)

June 20, 2023 at 2:25 pm

#4215304
Reply To: Devil's Advocate

sean redmond wrote:
Jeff Moden wrote:
Then, there are releases like 2016 where they changed some things that cause "silent failures producing incorrect rounding" in the DateTime datatype.
And, for a change, I shall be...
--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)

June 19, 2023 at 3:25 pm

#4214571
Reply To: Calculating Prime Numbers With One Query

Jonathan AC Roberts wrote:
I tried your script with 10 million rows and it took 25 seconds with dbo.fnTally using 126 MB log file. With GENERATE_SERIES it took it took 126 seconds using...
--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)

June 18, 2023 at 8:00 pm

#4214000
Reply To: Devil's Advocate

Heh... this reminds me of every bloody release since and including 2005. The ones before that didn't seem nearly as bad. Maybe because there were fewer moving parts.
The spooky part...
--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)

June 18, 2023 at 5:02 pm

#4213956
Reply To: Best Practice Analyzer for SQL Server 2019

Heh... or resolve yourself to the fact that no one will make any changes to the code no matter what you find and just let them do the testing they're...
--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)

June 18, 2023 at 4:52 pm

#4213929
Reply To: Calculating Prime Numbers With One Query

Jonathan AC Roberts wrote:
From my testing SQL Server's GENERATE_SERIES is efficient and well-optimized. It's not surprising it performers better than user defined functions as it is implemented in the native code 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)

June 18, 2023 at 4:48 pm

#4213928
Reply To: How Can I Speed This Query Up?

It turns out that you had an "anomaly" at the end of your link. Here's the correct link...
https://www.brentozar.com/pastetheplan/?id=B1Ons1TUn

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

June 18, 2023 at 5:27 am

#4213674
Reply To: How Can I Speed This Query Up?

water490 wrote:
water490 wrote:
water490 wrote:
frederico_fonseca wrote:
option 1 is incorrect - just discard it.
can you give the plan for the second? was expecting it to be a bit faster than that.
no problem
here...
--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)

June 18, 2023 at 5:12 am

#4213673
Reply To: Best Practice Analyzer for SQL Server 2019

David Burrows wrote:
Knowing your feelings on so called SQL Server best practices, Jeff, would you trust a tool like this if it existed 😏
Heh... You DO know me. That also means...
--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)

June 17, 2023 at 11:14 pm

#4213548
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
That's the article which had shaped my low expectations. Good to know it's been improved. It's also interesting Jonathan used GENERATE_SERIES in place of the non-itvf method of TOP...
--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)

June 17, 2023 at 11:02 pm

#4213533
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
These are great comparisons. The so-far winning code was jointly arrived at. Interesting in a straight comparison GENERATE_SERIES appears faster than fnTally
According to some testing that I saw (IIRC)...
--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)

June 17, 2023 at 2:01 am

#4213013

Viewing 15 posts - 1,381 through 1,395 (of 59,082 total)