is there a way to generate identity or sequence th

Question

In my code i tend to use a lot of Flags enums that correspond to a DB table that contain all their behavioral properties. This way the behavioral properties can be use both in SP and in C# code that uses the enum.

My problem is with Enums that have the Flags attribute attached to them.

Is there a way to automatically generate powers of 2 as identity column (or as a sequence) for such tables?

PS: currently i manually place the code in small table - or programmatically in tables that change over time - i prefer to do it automatically

Answer 1

No: read here

but you could keep the identity as a surrogate key then you use another column which is always 2^id (use a trigger on insert?) for your 2^n values or you could just use the plain id, after all if you are using the Flags attribute you just need the exponent, right?

Answer 2

In the 'Identity Specification' section on a column properties (visible after clicking 'Design' on your table), you can set the 'Identity Increment' to 2, to generate ids of 1, 3, 5, 7 if that's what you're after?

Answer 3

As far as I know, the answer is NO. Identity is meant to increment by the value of seed you specified which is 1 by default. But you can have identity in multiple of 2. e.g. 2,4,6,8,10

 id INT identity(2, 2)   -- starts at 2, increments by 2

If you want something else you might need to implement your own.

Computed Columns

create table t
(
   id int identity, 
   power_id as power(2, id), 
   name varchar(50)
)

Results

ID  POWER_ID    TEXT
-------------------------------
1   2           SHORT      ----2^1
2   4           MEDIUM     ----2^2
3   8           LONG       ----2^3