Column To generate Flag Values for use in bitwise comparisons
2
votes
2
answers
4978
views
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
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
SELECT
query:

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?
Asked by ΩmegaMan
(409 rep)
Mar 10, 2015, 03:27 PM
Last activity: Nov 10, 2019, 07:47 PM
Last activity: Nov 10, 2019, 07:47 PM