Create Sequence in MS SQL Server 2008

Question

I have written a program where i can do a Request for an identification card.

There are different types of identification cards ( Red, Blue, Green )

While the request, the program should generate identification numbers. The numbers (Range of the numbers) depends on which card are requested.

Red Card: 1 - 50000 
Blue Card: 50001 - 100000 
Green Card: 100001 - 150000

If i add new identification cards to the system so the sequence should automatically create a new Range of numbers for that new added identification card. The Numbers should not recur. One Number should only be used one time.

How can i do that? Can anyone help me with that?

Answer 1

You can use instead of insert trigger for this

create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));

insert into Cards_Types
select 'RED', 0 union all
select 'BLUE', 50000 union all
select 'GREEN', 100000;

create trigger utr_Cards_Insert on Cards
instead of insert as
begin
    insert into Cards (id, Color)
    select
        isnull(C.id, CT.Start) + row_number() over(partition by i.Color order by i.id),
        i.Color
    from inserted as i
        left outer join Cards_Types as CT on CT.Color = i.Color
        outer apply (
            select max(id) as id
            from Cards as C
            where C.Color = i.Color
        ) as C
end

sql fiddle demo

It allows you to insert many rows at once:

insert into Cards (Color)
select 'GREEN' union all
select 'GREEN' union all
select 'RED' union all
select 'BLUE'

Note that you'd better have index on Cards columns Color, ID.

Also note that your way you can insert only 50000 records for each type. You can use different seeds, for example 1 for 'RED', 2 for 'BLUE' and so on, and reserve place for , for example, 100 types of cards:

create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));

insert into Cards_Types
select 'RED', 1 union all
select 'BLUE', 2 union all
select 'GREEN', 3;

create trigger utr_Cards_Insert on Cards
instead of insert as
begin
    insert into Cards (id, Color)
    select
        isnull(C.id, CT.Start - 100) + row_number() over(partition by i.Color order by i.id) * 100,
        i.Color
    from inserted as i
        left outer join Cards_Types as CT on CT.Color = i.Color
        outer apply (
            select max(id) as id
            from Cards as C
            where C.Color = i.Color
        ) as C
end;

sql fiddle demo

this way ID for 'RED' will always ends on 1, ID for 'BLUE' ends on 2 and so on.

Answer 2

From the design perspective, I would strongly discourage coding additional logic into identifiers i.e. assigning card color to a specific range. I would rather use IDENTITY column that handles uniqueness and concurrency well, make IDs completely surrogate and store card color information for a given ID in another attribute. Possibly create an index on that additional attribute to retrieve records for a given color.

Also think about what would be needed if an owner of, say, red card requested to change it to a blue one? With ranges, to retain colors assignment you would need to create a new id and perhaps store somewhere else the information about old-to-new id sequence. What if someone changes it multiple times? With surrogate ID you can simply have one ID all the time to be able to track the same person through whole history and perhaps just add date information to your table to order changes sequentially. And this is just an example of a simple scenario.