Create Sequence in MS SQL Server 2008

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.

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.

You could leverage SQL Server's IDENTITY mechanism for this, because it's easy to use and handles concurrency well.

More specifically, you could create three tables that contain only an identity (auto-incremented) Id column, using this script:

create table RedCardIds(Id int identity(1, 1) primary key)
create table BlueCardIds(Id int identity(50001, 1) primary key)
create table GreenCardIds(Id int identity(100001, 1) primary key)
GO

The three tables have their identity values set to match your interval lower bounds.

Then, for every request you'd make an insert into the appropriate table and use the OUTPUT clause to get the newly generated identity value.

For example, if the request was for a Red Card, you could write:

insert RedCardIds 
output inserted.Id
default values

which would output:

Id
-----------
1

(1 row(s) affected)

On the next run it'll return 2, and so on.

Similarly, the first request for a Blue Card would trigger the statement:

insert BlueCardIds 
output inserted.Id
default values

with the result:

Id
-----------
500001

(1 row(s) affected)

Ideally you would have to maintain a table to store this information.

CardCategry MinNumber MaxNumber RunningNumber

Then you can write a SP to get next number and pass card category as a parameter. A sample query would be as follows.

SELECT @count=count(RunningNumber)
FROM IdentificationTable
WHERE CardCategry=@param

IF (@count=1)
    SELECT @RunningNumber=RunningNumber
    FROM IdentificationTable
    WHERE CardCategry=@param
ELSE
    SELECT TOP 1 @min=MinNumber,@max=MaxNumber
    FROM IdentificationTable
    ORDER BY MinNumber DESC

    INSERT INTO IdentificationTable VALUES (@param,@max+1,@max+(@max-@min),1)
    SET @RunningNumber=1

RETURN @RunningNumber

This is not a complete work.Obviously you would have to do some error handling for check boundary limit etc.

I would try something like this:

declare @cat2start int = 50000
declare @cat3start int = 100000

declare @catName varchar(10) = 'Red'

if @catName = 'Green'
    begin
    select (max(cardnumber) + 1) as [This is the next number]
    from yourTable 
    where 
    cardnumber < @cat2start
end
if @catName = 'Blue'
    begin
    select (max(cardnumber) + 1) as [This is the next number]
    from yourTable 
    where 
    cardumber >= @cat2start and cardnumber < @cat3start
end
if @catName = 'Red'
    begin
    select (max(cardnumber) + 1) as [This is the next number]
    from yourTable 
end

There are a lot of answers but I will add my 2 cents. Note that I'm assuming what I've written in my comment to your original post:

create table cardTypes(cardTypeName varchar(100) primary key, [50kSlot] int unique)

create table cards (identificationNumber bigint primary key);

--add slot if needed
declare @cardToBeAdded varchar(100) = 'green'
declare @tbl50kSlot table (i int)
merge into cardTypes as t
using (select @cardToBeAdded as newCard) as s
on t.[cardTypeName] = s.newCard
when not matched by target then
insert (cardTypeName, [50kSlot]) values (s.newCard, isnull((select max([50kSlot]) + 1 from cardTypes),1))
when matched then
update set [50kSlot] = [50kSlot]
output inserted.[50kSlot] into @tbl50kSlot;

declare @50kSlot int = (Select i from @tbl50kSlot)

insert into cards (identificationNumber) values (isnull(
    (select max(identificationNumber)+1 from cards where identificationNumber between ((@50kSlot-1)*50000+1) and @50kSlot*50000),
    (@50kSlot-1)*50000+1)
)

Of course you need to add some actual data to the cards table. Notice that the last query could be performed relatively fast if there is sufficiently effective index present. It may be worth to work around indexing the identificationNumber if there will be issues with performance. Consider - for example - creation of filtering index on this column if you will have a LOT of rows.

Alternatively you could keep maxInt in in cardTypes table and make a merge table slightly more complex. The downside is that if there will be some kind of error in between the queries the number will never be used so my solutions keeps sequence tight.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([color] varchar(10), [id] int)
;

INSERT INTO Table1
    ([color], [id])
VALUES
    ('Red',(select isnull(case when (max(id)/50000)%3 = 1 and 
                                max(id)%50000 = 0 then max(id)+100000 else
                                max(id) end,0)+1 
              from Table1 where color = 'Red'));

INSERT INTO Table1  ([color], [id]) VALUES  ('Red',50000);

INSERT INTO Table1
    ([color], [id])
VALUES
    ('Red',(select isnull(case when (max(id)/50000)%3 = 1 and 
                                max(id)%50000 = 0 then max(id)+100000 else
                                max(id) end,0)+1 
              from Table1 where color = 'Red'));

INSERT INTO Table1
    ([color], [id])
VALUES
    ('Blue',(select isnull(case when (max(id)/50000)%3 = 2 and 
                                max(id)%50000 = 0 then max(id)+100000 else
                                max(id) end,50000)+1 
              from Table1 where color = 'Blue'));

INSERT INTO Table1
    ([color], [id])
VALUES
    ('Green',(select isnull(case when (max(id)/50000)%3 = 0 and 
                                max(id)%50000 = 0 then max(id)+100000 else
                                max(id) end,100000)+1 
                from Table1 where color = 'Green'));

Query 1:

SELECT *
FROM Table1

Results:

| COLOR |     ID |
|-------|--------|
|   Red |      1 |
|   Red |  50000 |
|   Red | 150001 |
|  Blue |  50001 |
| Green | 100001 |

And here is my contribution to the challenge. Needs no extra table, should be concurrent-safe and can handle bulk updates. Might not be the fastest, but it works. It basically copies the rows to be inserted into a separate table, creates the IDs per color and finally moves everything to the destination table.

Create Trigger Trg_CreateID ON  dbo.Cards instead of insert
as
begin
  set nocount on
  -- declare a working table holding intermediate results
  declare @Tmp Table (cardID int, cardColor char(1), cardNumber char(20))

  -- copy the data to be inserted in our working table
  insert into @Tmp select * from inserted

  declare @Id int
  -- fill in the Id's once per color    
  select @Id=coalesce (max(cardID),0) from dbo.Cards where cardColor='Red'
  update @Tmp set cardID = @Id, @Id=@id+1 where cardColor='Red'

  select @Id=coalesce(max(cardID),50000) from dbo.Cards where cardColor='Blue'
  update @Tmp set cardID = @Id, @Id=@id+1 where cardColor='Blue'

  select @Id=coalesce(max(cardID),100000) from dbo.Cards where cardColor='Gree'
  update @Tmp set cardID = @Id, @Id=@id+1 where cardColor='Green'

  -- do the actual insert here
  insert into dbo.Cards select * from @tmp
end

it assumes a table Cards like this

CREATE TABLE [dbo].[Cards]
(
    [cardID] [int] NOT NULL,
    [cardColor] [char](1) NOT NULL,
    [cardNumber] [char](20) NOT NULL
) ON [PRIMARY]

I added a constraint to the cardID column to allow omitting it in insert statements

ALTER TABLE [dbo].[Cards] 
  ADD CONSTRAINT [DF_Cards_cardID] DEFAULT ((0)) FOR [cardID]

*This solution works for single row inserts, concurrency for multiple inserts needs different approach. Discussed in comments for more details *

If there is no options for creating tables then you can use instead of triggers (tweak like before triggers in oracle).

Use specific conditions inside a triggers to set the range of Identity column. Here's a sample how you can implement your solution.

Table

CREATE TABLE REQUEST_TABLE(
      REQ_ID numeric(8, 0) NOT NULL,
      REQ_COLOR VARCHAR(30) NOT NULL
 ); -- I have used this sample table

Instead of Trigger

CREATE TRIGGER tg_req_seq ON REQUEST_TABLE
INSTEAD OF INSERT AS
DECLARE @REQ_ID INT
DECLARE @REQ_COLOR VARCHAR(30)
DECLARE @REQ_START INT 
BEGIN  
  SELECT @REQ_COLOR= (SELECT ISNULL(REQ_COLOR,'NA') FROM INSERTED)

  SELECT @REQ_START = (SELECT CASE WHEN @REQ_COLOR = 'Red' THEN 0
                    WHEN @REQ_COLOR = 'Blue' THEN 50000 
                    ELSE 100000 END)

  SELECT @REQ_ID = ISNULL(MAX(REQ_ID),@REQ_START)+1 FROM REQUEST_TABLE   
    WHERE REQ_COLOR = @REQ_COLOR  

  INSERT INTO REQUEST_TABLE (REQ_ID,REQ_COLOR)
   VALUES (@REQ_ID,@REQ_COLOR)
END;

Now after some insert statements

INSERT INTO REQUEST_TABLE VALUES(NULL,'Red');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Red');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Red');

INSERT INTO REQUEST_TABLE VALUES(NULL,'Blue');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Blue');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Blue');

INSERT INTO REQUEST_TABLE VALUES(NULL,'Yellow');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Yellow');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Yellow');

I have added the same results in SqlFiddle. Let me know if i missed something to include.

Edit

Updated Fiddle to cater flexible requirement.