2

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:

enter image description here

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?

6
  • Do you want an exponential increment or just a static increment? What version of SQL Server are you using? – LowlyDBA - John M Mar 10 '15 at 15:31
  • @John Increment, either can be used. Version is 2012. I don't expect more than 15 flags(?)/rows of data, but an upper limit depending on target column type would be understood. – ΩmegaMan Mar 10 '15 at 15:33
  • Ignore the explanation at the top, but my answer here to use a sequence object should be what you want. – LowlyDBA - John M Mar 10 '15 at 15:35
  • @John I read the link to CREATE SEQUENCE but it is unclear to me on how to do a multiply operation on the INCREMENT BY <constant> specification. – ΩmegaMan Mar 10 '15 at 15:52
  • So you do want an exponential increment then? – LowlyDBA - John M Mar 10 '15 at 16:07
5

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;
3
  • I am looking for the PK to have the val value in my table; can you change your example table to not have the id value? I understand its key to its processing and this is a good system, but my goal is to have only one column the id column with your val value. I sense this example is similar to creating a Numbers only table to do select programming processing with the static numbers. – ΩmegaMan Mar 11 '15 at 13:55
  • 1
    @OmegaMan If you look closely you will see that val is the primary key. – Mikael Eriksson Mar 11 '15 at 13:59
  • Right it is ... I will keep this in mind. – ΩmegaMan Mar 11 '15 at 14:05
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.