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

  • Jonathan AC Roberts

    SSCoach

    Points: 16882

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

  • francesco.mantovani

    Right there with Babe

    Points: 789

    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: 442143

    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: 16882

    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: 994667

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994667

    @Jonathan,

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • francesco.mantovani

    Right there with Babe

    Points: 789

    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

    Right there with Babe

    Points: 789

    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: 16882

    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: 16882

    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.

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

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