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 - 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 1 year, 5 months ago by  Jonathan AC Roberts. Reason: Changed last line from [code]AND S.N < SQRT(p.N)[/code] to [code]AND S.N

A simplified version is found here https://www.sqltopia.com/mathematics/prime-number-generation/%5B/quote%5D

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

I think the script in the article is a bit of a non-starter.

It is calculating the square root multiple times for each row.

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

declare @n  ...

For exhaustive methods it's quicker to only look at odd numbers.  There was a Youtube video a little while ago where they compared all different programming languages to determine...

I wrote this SQL Server table valued function a few years ago. It returns all the prime numbers less than 1 million in about 1 second.

Thank you, Jonathan. Almost every step in the execution plan now marked as "No Join Predicate" and the whole function now costs 100% with my original tally one...