How to use multiple identity numbers in one table?

Question

I have an web application that creates printable forms, these forms have a unique number on them, the problem is I have 2 forms that separate numbers need to be created for them. ie)

Form1- Numbered 2000000-2999999
Form2- Numbered 3000000-3999999

dbo.test2 - is my form information table
Tsel - is my autoinc table for the 3000000 series numbers
Tadv - is my autoinc table for the 2000000 series numbers

What I have done is create 2 tables with just autoinc row (one for 2000000 series numbers and one for 3000000 series numbers), I then created a trigger to add a record to the coresponding table, read back the autoinc number and add it to my table that stores the form information including the just created autoinc number for the right series of forms.

Although it does work, I'm concerned that the numbers will get messed up under load. I'm not sure the @@IDENTITY will always return the right value when many people are using the system. (I cannot have duplicates and I need to use the numbering form show above.

See code below. ** TRIGGER **

CREATE TRIGGER MAKEANID2 ON dbo.test2
AFTER INSERT
AS
SET NOCOUNT ON
declare @someid int
declare @someid2 int
declare @startfrom int
declare @test1 varchar(10)

select @someid=@@IDENTITY

select @test1 = (Select name1 from test2 where sysid = @someid )
if @test1 = 'select'
begin
 insert into Tsel Default values
 select @someid2 = @@IDENTITY
end

if @test1 = 'adv'
begin
 insert into Tadv Default values
 select @someid2 = @@IDENTITY
end

update test2
set name2=(@someid2) where sysid = @someid
SET NOCOUNT OFF

Answer 1

The best way to keep the two IDs in sync is to create a persisted Computed Column based on the actual identity column. Where Col1 is the identity column and Col2 is the persisted computed column that is the result of some formula based on Col1. You can then even Create Indexes on Computed Columns.

test this out:

CREATE TABLE YourTable
(Col1 int not null identity(2000000,1)
,Col2 AS (Col1-2000000+3000000) PERSISTED
,Col3  varchar(5)
)
GO

insert into YourTable (col3) values ('a')
insert into YourTable (col3) SELECT 'b' UNION SELECT 'c'

SELECT * FROM YourTable

OUTPUT:

Col1        Col2        Col3
----------- ----------- -----
2000000     3000000     a
2000001     3000001     b
2000002     3000002     c

(3 row(s) affected)

EDIT After OPs comments, I'm still not 100% sure what you are after.

I never used SQL Server 2000 (we skipped that version), and I don't really want to look up how to do everything in that version, it is so limited without the OUTPUT clause and ROW_NUMBER(), CTEs, etc.

I can think of three methods to do:

1) You could just create a sequence table, where you have 2 rows one for A and one for B, each time you need to insert one, look up, increment, and save the value of the type of seq you need and then insert with that value. for example if you are inserting a type "A" row, do this:

INSERT INTO test2
        (col1, col2, col3,...)
    SELECT
        ISNULL(MAX(NextSeq),0)+1, col2, col3,...
        FROM YourSequenceTable WITH (UPDLOCK, HOLDLOCK)
        WHERE SequenceType='A'

UPDATE YourSequenceTable 
    SET NextSeq=ISNULL(NextSeq,0)+1
    WHERE SequenceType='A'

2) change your table structure to just save the data in Tsel or Tadv and have a trigger insert into a third common table table where you can have your additional "common" identity. common table would be like

CommonTable
ID      int not null indentity(1,1) primary key
TselID  int null FK to Tsel.PK 
TadvID  int null FK to Tadv.PK

3) if you need a single table, try this, which is a real hack. Change your Tsel and Tadv tables to contain all the necessary columns and from the application INSERT INTO Tsel when the value is select and have a trigger grab that identity value and then INSERT that into test2, then remove the data from tsel. Then, from the application when the value is adv just INSERT INTO Tadv an have a trigger on that table insert the data into test2, and remove the data from Tadv. You need to have all data columns in Tsel and Tadv so the trigger can copy the values to test2, but the trigger will remove the rows from there (the identity will be sequential even if the original rows are removed).

your Tsel trigger would look like:

CREATE Trigger  MAKEANID2_Tsel ON dbo.Tsel
AFTER INSERT
AS

--copy data from Tsel into test2., test2 can still have its own identity value
INSERT INTO test2
        (PK, col1, col2, col3,...)
    SELECT
        col0, col1, col2, col3,....
        FROM INSERTED

--remove rows from Tsel, which were just copied and not needed anymore.
DELETE Tsel
    WHERE PK IN (SELECT PK FROM INSERTED)

GO

Answer 2

YOu are right to worry about @@identity, it is not a recommended peice of code, if somone else adds a differnet trigger that inserets an identity and that one triggers first, that is the value you will get.

But you have much bigger problems. Your trigger is deisgned to work on only one record ata time. This is a very very very bad thing to do with a trigger. Triggers operate on sets of data and must ALWAYS even if you think therer will never be more than one record inserted ata time) be set up to handle sets of data not one record. Further, you don;t need to ask for the identity, you have the identities of all records inserted inteh batch in a psuedotable availlble in triggers called inserted.

Now reading one of your comments, you say you can't have any missing values at all. Inthat case you cannot under any circustance use an identity column as it will have gaps if any transaction is rolled back. You will have to write your own process to create the numbers based onteh last number and look out for race conditions.