I work on sql server and VB.net I often use identity(1,1) on int primary key. But Today I want create a table to replace an enum. I use it in my programm like flags what can be adds (It's an enum about diseases, before I managed few diseases then You can have several diseases... but now I want to be able adds more diseases.)
I can do it in my vb codes, but I prefer my SQL server take care alone about his keys. But I don't find a way to say next key = last key << 1???
Thanks for your help.
SQL Server does not have a bit shift operator.
For as low as 15 records, I would recommend simply use a tinyint as your primary key and manually enter the values when you enter the next row.
You can have SQL Server compute it automatically for you, but that is an overkill to do it correctly.
A naive approach would be something like this:
CREATE TABLE disease
(
ident tinyint identity(1,1),
name varchar(100),
id AS (POWER(2, ident)) PERSISTED PRIMARY KEY
)
Test:
INSERT INTO disease (name) VALUES
('flu'), ('diabetes'), ('tonsillitis')
SELECT id, name
FROM disease
Results:
id name
2 flu
4 diabetes
8 tonsillitis
But it is naive because it assumes no gaps in the identity column. SQL Server does not guarantee that at all.
In order to do it correctly you will have to calculate the id column using a user defined function that will actually count the number of records entered before the current one, and then return the power of 2 by that number.
Please note that in this case you can't have the computed column persisted so it can't be the primary key.
CREATE TABLE disease
(
ident tinyint identity(1,1) PRIMARY KEY,
name varchar(100),
);
GO
CREATE FUNCTION fn_CalculateDiseaseId
(
@ident tinyint
)
returns smallint
AS
BEGIN
RETURN
POWER(2,
(
SELECT COUNT(*)
FROM disease
WHERE ident < @ident
) +1
)
END;
GO
ALTER TABLE disease
ADD id AS dbo.fn_CalculateDiseaseId(ident);
GO
same test as before:
INSERT INTO disease (name) VALUES
('flu'), ('diabetes'), ('tonsillitis')
SELECT id, name
FROM disease
Results:
id name
2 flu
4 diabetes
8 tonsillitis