Column To generate Flag Values for use in bitwise comparisons 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 &amp; 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?
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;