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)

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

By Jonathan Roberts,

This is an improvement to my last script: http://www.sqlservercentral.com/scripts/Tally+Table/155213/

Instead of deleteting rows from a table variable it uses a NOT EXISTS to filter out rows that are not prime, it also has a better filter to eliminate some obvious non-primes when populating the table variable @N.

Initially I tried writing the process as a script using temporary tables instead of table variables; when the script is written like this it runs approximately twice as fast (a demonstration of why temporary tables are better than table variables). But a table vaued function cannot have temporary tables in its code.

Of course, the fastest way to get lists of prime numbers, in SQL Server, is to create a permanant table of primes on your database which can be achieved this using this table valued funtion:

IF OBJECT_ID('dbo.Primes','U') IS NOT NULL 
    DROP TABLE dbo.Primes
INSERT INTO dbo.Primes(N)
  FROM dbo.FastPrimes(100000000,2)
-- Wait for 10 minutes to get all 5,761,455 prime numbers less than 100 million into a permanant table table, using 1.3 GB, that you can then use with great speed at your leisure.
SELECT * FROM dbo.Primes

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

Temporary variables in SQL

Improve the SQL query performance with temporary variables


Finding Primes

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


Local Temporary Tables and Table Variables

Peter He examines the differences between temporary tables and table variables in SQL Server, showin...


SQL Table variable and temporary table

Table variables and Temporary tables  are used interchangeably – but they were designed for differen...


Simplify Large Queries with Temporary Tables, Table Variables and CTEs

This article demonstrates how to manage the development of complex queries by building them in stage...

prime numbers