SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Table Valued Function to return a range of prime numbers

By Jonathan Roberts,

I just wrote this function just for the fun of it. 

It uses the sieve of Eratosthenes algorithm to find ranges of prime numbers and is a really fast method to generate lists of primes.

It also uses a dynamic inline tally table which is a modification of this http://www.sqlservercentral.com/articles/T-SQL/67899/ by Lynn Pettis

To get a list of primes between 1,000 and 10,000 just run the SQL statement: 

SELECT * FROM dbo.DynamicPrimes(10000, 1000) 

To find the count of primes between 2 and 100,000 just run:

SELECT COUNT(*) CountPrimes FROM dbo.DynamicPrimes(100000, 2) 


Total article views: 328 | Views in the last 30 days: 3
Related Articles

Finding Primes

While it's not likely that many of you need to find prime numbers using T-SQL, it is an interesting ...


Creating Stored Procedure in SQL server 2000 for Printing Prime Numbers

"Trying to create a sp that prints Prime numbers upto 500"


Prime Number Table Generator (new ver crunches to 1 Mil in <10.5 sec!)

Generates prime numbers and puts them into a table for future reference by adhoc queries and databas...



Dear all, I have SP : [code language="sql"] CREATE proc [dbo].[usp_FindPrimes] (@opt tinyint,...

prime numbers    
tally table