The amount of computation could be reduced by restricting the list of divisors to just the prime numbers already found that are <= SQRT(candidate). The problem is that the...

Hmmm  imo the  issue of mathematical appropriateness of n = 6q + r is debatable.  I found this link which describes it a little.  Without complete analysis it...

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

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

I installed SQL Server 2022 today and changed my version to use GENERATE_SERIES. I also amended Steve Collins algorithm to use it and remove all the multiplications by "*2+1" as...

At the moment I don't have access to an instance of SQL 2022 to test with.  That could change in the near future tho.  For now I was just...

Why mess with fnTally when what we really need is for Jeffrey to rewrite the query?  Ha, then we also get a baseline of the functions.  Also how does...

I haven't yet installed SQL Server 2022 and thought it would be possible to write a TVF with the same functionality as SQL 2022's GENERATE_SERIES so I could...

@jonathon-2 - I just tested your method and found that it is including 151 additional non-prime numbers in the results.  I also tested both version on one of my...

This is the dynamic SQL that is created:

SELECT VID, [2022-01-10], [2022-01-11], [2022-01-12]
 ...

Here's another one query solution.

Finished in about 3 seconds for prime numbers up to 1 million (on my machine).

I can't test it against SwePeso's solution as I...

• This reply was modified 2 years, 2 months ago by Jonathan AC Roberts. Reason: Changed last line from [code]AND S.N < SQRT(p.N)[/code] to [code]AND S.N

Very good and a clear explanation of how it works.

CREATE TABLE #t1([VID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [FldVal] int NULL ) ON [PRIMARY]

insert into #t1 values('111','2022-01-10',10)
insert into #t1 values('111','2022-01-11',1)
insert into #t1 values('111','2022-01-12',2)

-- Generate dynamic column names...

Peter "Peso" Larsson wrote a nasty fast Prime Numbers function years ago and I tested it to confirm it.  I'll see if I can find it in my archives...

You only need to test up to the square root of a number to test for primality, which is quite a saving:

declare @n  ...