Database Programming: Denis' Prime Number Challenge

Here's The CREATE DATABASE Script The SQLMS Generated

Ward Pond — Sat, 23 Sep 2006 12:31:52 GMT

CREATE DATABASE [PrimeNumberTest] ON PRIMARY (
NAME = N'PrimeNumberTest',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PrimeNumberTest.mdf',
SIZE = 3GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1GB )
LOG ON (
NAME = N'PrimeNumberTest_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PrimeNumberTest_log.ldf',
SIZE = 5GB,
MAXSIZE = 2048GB,
FILEGROWTH = 1GB )
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'PrimeNumberTest', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [PrimeNumberTest].[dbo].[sp_fulltext_database] @action = 'disable'
END
GO
ALTER DATABASE [PrimeNumberTest] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PrimeNumberTest] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PrimeNumberTest] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PrimeNumberTest] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PrimeNumberTest] SET ARITHABORT OFF
GO
ALTER DATABASE [PrimeNumberTest] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PrimeNumberTest] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [PrimeNumberTest] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PrimeNumberTest] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PrimeNumberTest] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PrimeNumberTest] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [PrimeNumberTest] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PrimeNumberTest] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PrimeNumberTest] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PrimeNumberTest] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PrimeNumberTest] SET ENABLE_BROKER
GO
ALTER DATABASE [PrimeNumberTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PrimeNumberTest] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PrimeNumberTest] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PrimeNumberTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PrimeNumberTest] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PrimeNumberTest] SET READ_WRITE
GO
ALTER DATABASE [PrimeNumberTest] SET RECOVERY FULL
GO
ALTER DATABASE [PrimeNumberTest] SET MULTI_USER
GO
ALTER DATABASE [PrimeNumberTest] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [PrimeNumberTest] SET DB_CHAINING OFF
GO

re: Database Programming: Denis' Prime Number Challenge

Denis the SQL Menace — Sat, 23 Sep 2006 13:36:13 GMT

Well here is a Sieve of Eratosthenes version, runs in 6 seconds on 2nd run, first run will be around 16 seconds on my laptop (SQL 2000)

SET NOCOUNT ON

DECLARE @i INT

-- Create a 10-digit table
DECLARE @D TABLE (N INT)
INSERT INTO @D (N)
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4

INSERT INTO @D (N)
SELECT N+5 FROM @D

-- build a small sieve table between 2 and 1000
DECLARE @T TABLE (N INT)
INSERT INTO @T( N )
SELECT 1+A.N+10*(B.N+10*C.N)
FROM @D A, @D B, @D C

DELETE FROM @T WHERE N = 1

SET @I = 2
WHILE @I <= SQRT(1000) BEGIN DELETE FROM @T WHERE N % @I = 0 AND N > @I
SET @I = @I + 1
END

-- Create large table between 1001 and 1000000
SELECT A+10*(B+10*(C+10*(D+10*(E+ 10*F)))) AS N
INTO #P
FROM
( SELECT A.N AS A, B.N AS B, C.N AS C, D.N AS D, E.N AS E, F.N AS F
FROM @D A, @D B, @D C, @D D, @D E, @D F
WHERE A.N in (1, 3, 7, 9) -- Not divisible by 2 or 5
) bNum
WHERE (A+B+C+D+E+F) % 3 <> 0 -- Or 3
AND (A+3*B+2*C-D-3*E-2*F) % 7 <> 0 -- Or 7
AND (B-A+D-C+F-E) % 11 <> 0 -- Or 11
and D|E|F <> 0 -- Don't include the first 1000 numbers,
--we already have these in the small sieve table
UNION ALL SELECT 1000000

-- sieve the big table with smaller one
SELECT @I = 2
WHILE @I IS NOT NULL
BEGIN
DELETE FROM #P WHERE N% @I = 0
SELECT @I = MIN(N) FROM @T WHERE N > @I
END

-- add primes up to 1000
INSERT INTO #P SELECT N FROM @T

-- Here are the results
--78498 rows
SELECT * FROM #P ORDER BY 1

drop table #P
go

The prime number challenge – great waste of time!

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' — Sun, 24 Sep 2006 01:56:04 GMT

No sane person would even consider using SQL Server to construct a list of prime numbers. So just to...

re: Database Programming: Denis' Prime Number Challenge

Denis The SQL Menace — Sun, 24 Sep 2006 03:19:49 GMT

Let me know how many seconds this one takes?

SET NOCOUNT ON

DECLARE @i INT

-- Create a 10-digit table
DECLARE @D TABLE (N INT)
INSERT INTO @D (N)
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4

INSERT INTO @D (N)
SELECT N+5 FROM @D

-- build a small sieve table between 2 and 1000
DECLARE @T TABLE (N INT)
INSERT INTO @T( N )
SELECT 1+A.N+10*(B.N+10*C.N)
FROM @D A, @D B, @D C

DELETE FROM @T WHERE N = 1

SET @I = 2
WHILE @I <= SQRT(1000) BEGIN DELETE FROM @T WHERE N % @I = 0 AND N > @I
SET @I = @I + 1
END

-- Create large table between 1001 and 1000000
SELECT A+10*(B+10*(C+10*(D+10*(E+ 10*F)))) AS N
INTO #P
FROM
( SELECT A.N AS A, B.N AS B, C.N AS C, D.N AS D, E.N AS E, F.N AS F
FROM @D A, @D B, @D C, @D D, @D E, @D F
WHERE A.N in (1, 3, 7, 9) -- Not divisible by 2 or 5
) blah
WHERE (A+B+C+D+E+F) % 3 <> 0 -- Or 3
AND (A+3*B+2*C-D-3*E-2*F) % 7 <> 0 -- Or 7
AND (B-A+D-C+F-E) % 11 <> 0 -- Or 11
AND D|E|F <> 0 -- Don't include the first 1000 numbers,
--we already have these in the small sieve table
UNION ALL SELECT 1000000

-- sieve the big table with smaller one
SELECT @I = 2
WHILE @I IS NOT NULL
BEGIN
DELETE FROM #P WHERE N% @I = 0
SELECT @I = MIN(N) FROM @T WHERE N > @I
END

-- add primes up to 1000
INSERT INTO #P SELECT N FROM @T

-- Here are the results
--78498 rows
SELECT * FROM #P ORDER BY 1

drop table #P
go

Database Programming: Denis' Prime Number Challenge: A New Leader

Ward Pond's SQL Server blog — Sun, 24 Sep 2006 10:01:02 GMT

Well, folks, there's a new sheriff in town..&nbsp; this is a continuation of a conversation here and...

Database Programming: Sunday Night Prime Number Update

Ward Pond's SQL Server blog — Mon, 25 Sep 2006 05:19:33 GMT

Our blogging site is going to go dark in an hour or so for a software upgrade, so I won't be able to...

Database Programming: Closing The Door On The "Discredited" Prime Number Tangent

Ward Pond's SQL Server blog — Tue, 26 Sep 2006 03:08:16 GMT

One more PDA-incompatible post: lessons learned from "discredited" prime number syntax

Database Programming: A Prime Number Contender From Down Under

Ward Pond's SQL Server blog — Sat, 30 Sep 2006 11:55:01 GMT

Denis' Prime Number Challenge just won't die.&nbsp; I think this topic has spurred more dialog than any&nbsp;other...

Database Programming: Closing The Door On The "Discredited" Prime Number Tangent

Ward Pond's SQL Server blog — Wed, 11 Oct 2006 08:20:25 GMT

One more PDA-incompatible post: lessons learned from "discredited" prime number syntax

A "Kobiyashi Maru" On The Prime Number Front?

Ward Pond's SQL Server blog — Tue, 17 Oct 2006 03:41:41 GMT

No less a figure than Louis Davidson has weighed in on the Prime Number Challenge . I left a comment

Database Programming: Denis' Prime Number Challenge

Here's The CREATE DATABASE Script The SQLMS Generated

re: Database Programming: Denis' Prime Number Challenge

The prime number challenge – great waste of time!

re: Database Programming: Denis' Prime Number Challenge

Database Programming: Denis' Prime Number Challenge: A New Leader

Database Programming: Sunday Night Prime Number Update

Database Programming: Closing The Door On The &quot;Discredited&quot; Prime Number Tangent

Database Programming: A Prime Number Contender From Down Under

Database Programming: Closing The Door On The "Discredited" Prime Number Tangent

A "Kobiyashi Maru" On The Prime Number Front?

Database Programming: Closing The Door On The "Discredited" Prime Number Tangent