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
GO
 CREATE TABLE dbo.Primes(N int PRIMARY KEY CLUSTERED);
GO
INSERT INTO dbo.Primes(N)
SELECT Prime
  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: 324 | Views in the last 30 days: 18
 
Related Articles
FORUM

Temporary variables in SQL

Improve the SQL query performance with temporary variables

ARTICLE

Finding Primes

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

ARTICLE

Local Temporary Tables and Table Variables

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

BLOG

SQL Table variable and temporary table

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

ARTICLE

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

Tags
prime numbers    
 
Contribute