Column To generate Flag Values for use in bitwise comparisons

Question

We are in the process of creating a new application where various tables are used to hold enumerations. Since the IDs in these tables will be used in bitwise comparisons, we'd like to ensure new rows inserted will automatically use the decimal number that corresponds to the next bit being "set".

For instance, if we had an alarm clock application, we might include a "Weekdays" table which could be used in the following way:

CREATE TABLE dbo.Weekdays
(
    WeekdayID INT NOT NULL 
        CONSTRAINT PK_Weekdays PRIMARY KEY CLUSTERED
    , WeekdayName VARCHAR(20)
);

INSERT INTO dbo.Weekdays
VALUES (1, 'Sunday')
    , (2, 'Monday')
    , (4, 'Tuesday')
    , (8, 'Wednesday')
    , (16, 'Thursday')
    , (32, 'Friday')
    , (64, 'Saturday');

CREATE TABLE dbo.AlarmsDefined
(
    AlarmID INT NOT NULL
        CONSTRAINT PK_AlarmsDefined PRIMARY KEY CLUSTERED
    , Weekdays INT NOT NULL
);

INSERT INTO dbo.AlarmsDefined (AlarmID, Weekdays)
VALUES (1, 50);

SELECT W.WeekdayName
FROM dbo.AlarmsDefined AD
    INNER JOIN dbo.Weekdays W ON (AD.Weekdays & W.WeekdayID) = W.WeekdayID

The results of the above SELECT query:

I'd like to be able to define the Weekdays table in the above example such that if we added another row to the table, the next WeekdayID would be automatically set as 128, which is the next decimal number that can be used in bitwise comparisons.

This of course can be accomplished by putting in the values by hand into the primary key column.

But is there a way to specify an automatically incrementing value which would adhere to the flag values? For example these would be the unique PK values generated:

ID
--
1
2
4
8
16
32
...

and the trigger would automatically look at the last value and generate the next in the sequence needed?

Answer 1

I would use something like this:

CREATE TABLE #test 
(
    id INT IDENTITY(0,1) NOT NULL
    , val AS (POWER(2,id)) PERSISTED
    , CONSTRAINT pk_test PRIMARY KEY
        (
            val
        )
);
GO

INSERT #test DEFAULT VALUES;
GO 15

SELECT * FROM #test;

Answer 2

The ideal way to be incrementing a primary key, as I noted in the comments, is to use the new SEQUENCE object in SQL 2012. It'll minimize the overhead of this generation process and is the best option in most cases.

However, if you truly have to have exponentially increasing keys, then using a trigger will work. This may be better served in a stored procedure using the same logic, but it's easier to demonstrate via a trigger as I don't have more information as to how these inserts are occurring in your system.

You can do an INSTEAD OF INSERT trigger since it looks like this is a primary key and probably will be NOT NULL as a result:

CREATE TRIGGER [tr_setPK]
ON [Table] INSTEAD OF INSERT
AS 
    BEGIN
        DECLARE @newID INT
        SET @newID = SELECT MAX(ID) * 2 FROM [Table]
        INSERT INTO [Table] 
            VALUES(@newID, Inserted.column1, Inserted.column2 ....)
    END
GO

or use an AFTER INSERT trigger if NULL isn't a concern (though it should be):

CREATE TRIGGER [tr_setPK]
ON [Table] AFTER INSERT 
AS 
    BEGIN
        SET ID = MAX(ID) * 2 FROM [Table]
    END
GO

These aren't going to be ideal for scaling up, but should do the trick.