T-SQL Identity Seed Expression

Question

Is it possible to use an expression for identity seeds in T-SQL? We're putting a bunch of tables together, using BIGINT for the ID columns, but want to stagger the seeds so that the tables are not overlapping their ID number spaces.

This is much easier to do using hex values, so we can mask out the highest two bytes or so, such as seed 1 * 0x1000000000000 for table1 and seed 2 * 0x1000000000000 for table2 etc. - thereby still leaving plenty of possible IDs available for each table.

The problem here is, SQL doesn't like seeing the hex values or multiplication in the IDENTITY statement, so we tried manually casting them to BIGINT but the error persisted: "incorrect syntax, expected INTEGER or NUMERIC."

It seems like T-SQL doesn't want to see anything other than a single literal value, in decimal (not hex), with no math operations.
We can deal with this, by doing the math ourselves and converting the numbers to decimal - but we'd like to avoid this if possible, since the numbers are more difficult to keep track of in decimal format - bug prone, etc.

(I should explain, bug-prone, because we use these values to determine which table an object belongs to, based solely on it's ID value being in the appropriate number space - those first two bytes being a sort of "table ID")

However, is there another way to accomplish what I'm describing using hex values and multiplication, while using some weird syntax that T-SQL can accept?
I know this is an inconvenience, not a blocking issue, but I want to make sure there truly aren't any alternatives before we settle on this workaround.

Answer 1

Just blend bad ideas by using dynamic SQL:

declare @Bar as BigInt = 0x1000000000000;
declare @Foo as NVarChar(1000) = 'dbcc checkident(''Foo'', RESEED, ' + cast( 2 * @Bar as NVarChar(64) ) + ')';
exec sp_executesql @Foo;

I'm sure a RegEx would improve it.

Answer 2

When you create the table you can set the initial seed value.

create table Table1(Id int Identity(10000000,1), Name varchar(255));

or you can use this statement on a table that is already created

DBCC CHECKIDENT ('dbo.Table1', RESEED, 20000000);

The next entry will be 20000001 assuming you have the step = 1

Answer 3

Create a trigger (before insert) for this table, and disable identity.

Convert Hex to int: Convert integer to hex and hex to integer

Example:

CREATE TRIGGER TRIGGER_NAME 
ON TABLE_NAME 
INSTEAD OF INSERT
AS
BEGIN
    IF (SELECT ID FROM INSERTED) IS NULL
    BEGIN
        DECLARE @INITIAL_ID BIGINT = (SELECT CONVERT(BIGINT, 0x1000000000000) * 2)
        DECLARE @NEW_ID BIGINT = 
            ISNULL( (SELECT MAX(ID) FROM TABLE_NAME) + 1, @INITIAL_ID )
        SELECT * INTO #INSERTED FROM INSERTED
        UPDATE #INSERTED SET ID = @NEW_ID
        INSERT INTO TABLE_NAME SELECT * FROM #INSERTED
    END
    ELSE
    BEGIN
        INSERT INTO TABLE_NAME SELECT * FROM INSERTED
    END
END
GO