# SQL Puzzle – Prime Numbers

My goal here is to have something fun (and hopefully educational/thinky) (and yes, I did just make up the word thinky, live with it) at the end of each month. So this month it’s a puzzle. Calculate the first 10 prime numbers.

Definition of a prime number:

A Prime Number can be divided evenly only by 1, or itself. And it must be a whole number greater than 1.

Goals (since I can’t think of any real rules)

• Should be able to easily extend the solution past 10. i.e. if I asked you to come up with the first 20 prime numbers it would require minimal changes to your code.
• This is T-SQL. Set based is always preferable over RBAR (loops). FYI this can be trickier than it sounds since technically things like recursive CTEs are actually RBAR behind the scenes (as I understand it).
• Speed! Speed is important. Prime numbers are one of those things that can get pretty large pretty quickly. Maybe your method will be the one the scientists use going forward to calculate prime numbers.
• The simpler the code the better. If you aren’t a junior DBA, write it so that one can read it.
• Comments as necessary. If your code is anything above junior level then it needs comments. It’s good to get into the habit even in simple code.

Here is my solution:

Using a numbers table (that I got from Aaron Bertrand’s (b/t) post http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

```SELECT TOP (10) Number
FROM Numbers
WHERE Numbers.Number > 1 -- We only want numbers > 1
AND NOT EXISTS (
SELECT 1 FROM Numbers N2
WHERE N2.Number > 1 -- We only want numbers > 1
-- We only need to test numbers less than the current number
AND Numbers.Number > N2.Number
-- Check to see if there are any numbers that evenly divide in.
AND Numbers.Number % N2.Number = 0
)```

And I get the answer: 2, 3, 5, 7, 11, 13, 17, 19, 23, 29

Now the down side here is that my numbers table has to be big enough to cover any number I’m going to test. Eventually that could require a pretty large numbers table. I’ll leave it to you to decide if that’s important or not.

Filed under: DBA Humor, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: Humor, Puzzle

## SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.