Fast Table Valued Function to return all prime numbers within a range

Question

Fast Table Valued Function to return all prime numbers within a range

Jonathan AC Roberts

SSCoach

Points: 18362
More actions
April 4, 2017 at 5:24 am

#342098

Comments posted to this topic are about the item Fast Table Valued Function to return all prime numbers within a range

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 1

Hi Jonathan, I run your query and it says "Completed" but if then I run:
select * from [dbo].[FastPrimes]
it returns:
Parameters were not supplied for the function 'dbo.FastPrimes'.
How to select?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

francesco.mantovani - Monday, December 3, 2018 2:01 AM
Hi Jonathan, I run your query and it says "Completed" but if then I run:
select * from [dbo].[FastPrimes]
it returns:
Parameters were not supplied for the function 'dbo.FastPrimes'.
How to select?

You use the mtvf to populate a permanent table (as shown in the article the table is named dbo.Primes. After populating that table you would then query it instead of using the function dbo.FastPrimes.

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 3

francesco.mantovani - Monday, December 3, 2018 2:01 AM
Hi Jonathan, I run your query and it says "Completed" but if then I run:
select * from [dbo].[FastPrimes]
it returns:
Parameters were not supplied for the function 'dbo.FastPrimes'.
How to select?

Say you wanted all prime numbers between 2 and 10,000 you would type:
SELECT * FROM dbo.FastPrimes(10000, 2)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Just a thought...

This reminds me of the Factorial function. The same values will always be returned. With that in mind, it would probably be the best to store the inputs and the results in an NVP table and simply query the table for results. It's not like the list of Prime Numbers is going to change any time soon and, like a Tally Table, won't take very much room. If the system must remain "readless", then that may be another story but I can't imagine why a system would need to remain "readless".

So use Jonathan 's good function to generate such a table.

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

@jonathan-2,

Nice function. It seems like you've included just about every optimization for rejecting non-prime candidates there is.

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

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 6

Thank you Jonathan,
I'm interested in your query, I would like to create a database with factorized numbers like this one: http://www.factordb.com/
This database apparently it's only >400MB and it perfectly does the job I want: if I input "590632926550117049" it returns me the 2 primes that created that number: "57848543" and "10209987943".

Is it possible to create a query that creates 3 columns: 1 with the factorized number and the other 2 with the 2 primes that created that number?
Thank you

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 7

And also, another question: why are you creating a database with prime numbers?
As long as I know prime numbers are only used for cryptography, have they any other use?
Thank you

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 8

francesco.mantovani - Tuesday, December 4, 2018 2:34 AM
Thank you Jonathan,
I'm interested in your query, I would like to create a database with factorized numbers like this one: http://www.factordb.com/
This database apparently it's only >400MB and it perfectly does the job I want: if I input "590632926550117049" it returns me the 2 primes that created that number: "57848543" and "10209987943".
Is it possible to create a query that creates 3 columns: 1 with the factorized number and the other 2 with the 2 primes that created that number?
Thank you

Hi Francesco

I wrote a script for a function that will factor numbers, it involves first creating a large table of primes then installing the function.

https://www.sqlservercentral.com/scripts/table-valued-function-to-factorize-numbers-up-to-100-trillion

Thanks,

Jonathan

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 9

francesco.mantovani - Tuesday, December 4, 2018 2:37 AM
And also, another question: why are you creating a database with prime numbers?
As long as I know prime numbers are only used for cryptography, have they any other use?
Thank you

It was just a bit of fun really. I don't think SQL Server is really the best tool for this kind of application.