How to stop multiple of 13 appearing in an Identity Column

Question

Say I have the following table:

Create Table Comments (
    ID Int Identity(1,1) Not Null Primary Key Clustered,
    Comment Text Not Null
)

Since I'm superstitious, how can I stop multiples of 13 appearing in the ID column?
ie: Skip 13, 26, 39 etc.

Solution in MySQL or MSSQL much appreciated.

Answer 1

Create Table Comments (    
    ID Int Identity(2,2) Not Null Primary Key Clustered,    
    Comment Text Not Null
)

Answer 2

Create a trigger to skip to the next one every time 13n - 1 comes up in the sequence

BradC, this is for you. Without any knowledge of SQL Server, I'll do it in Oracle. This seems to be a good reference for triggers in SQL Server

CREATE OR REPLACE TRIGGER trigname 
  AFTER INSERT ON Comments
  FOR EACH ROW 
  IF (:new.ID % 13 = 12) THEN
    -- increase the sequence
    SELECT comment_ID_sequence.NEXTVAL FROM dual;
  END IF;   
END;

Without actually testing it, this will probably not work, but with a small amount of trial and error, you can get it working. Oracle has sequence objects that aren't tied to the table at all, and you can bump the sequence all day if you want, without ever touching the table. I don't know if this is true in SQL Server.

Answer 3

Edit: previous answer was completely wrong.

You can do it like this:

Identity(1, 13)

As tested by:

for (int i = 1; i < 10000000; i += 13)
{
    if (i % 13 == 0)
    {
     Console.WriteLine(i);
    }
}

Incrementing by 13, starting from 1 should never give you a multiple of 13, at least up to the 10 million.

Answer 4

Create a trigger on insert.

When inserting something which is a multiple of 13 minus 1 (12, 25, 38, etc.) insert and delete another row immediately.

Something like that (might need modifications):

CREATE TRIGGER ON [table_name]
AFTER INSERT
AS
    -- Get the last inserted identifier
    DECLARE @LastID INT -- or whatever type is your identity column
    SET @LastID = SELECT ID FROM inserted -- inserted holds the inserted entry

    -- Check if the ID is a multiple of thirteen minus 1
    IF ((@LastID + 1) % 13 = 0) -- not sure it would work, but something like that
    BEGIN
        INSERT INTO [table_name]
        -- dummy values

        DELETE FROM [table_name] WHERE ID = (@LastID + 1)
    END

GO

Answer 5

Identity(7919, 4966)

This returned 432'436 unique IDs within a 32bit int and none was a multiple of 13.

More pairs:

17, 1040 - Yields 2'064'889 values

17, 559 - Yields 3'841'653 values

[EDIT] Small python program to test:

import sys

def x(start, step):
    count = 0
    i = start
    N = 1 << 31
    while i < N:
        #print i
        if i % 13 == 0:
            break
        i += step
        count += 1
    print i, i/13.0, count

if __name__ == '__main__':
    x(int(sys.argv[1]), int(sys.argv[2]))

I just used a couple of primes but that didn't really work; with primes, I could only get sequences with 1-12 numbers. So I started with a random pair and varied the second number until the script would stop to return.

I have no idea of the mathematical properties of the two numbers ;) Anyone?