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.

That is one of teh most bizarre things I have ever heard of? Why on earth woudl you do that? I would suspect, your table structure is badly designed if you even want to do that. — HLGEM, Jun 25 '15 at 18:05
This idea is just the worst. You should not bastardize your identity like that. You are essentially trying to insert meaning into a meaningless value. If you really think you need to do this then you should consider using a sequence for each table. — Sean Lange, Jun 25 '15 at 18:19
I would suggest a better solution is to use the identities as they are intended. If you need to have a field that makes it clear which table something comes from then create a computed column that adds a letter(s) to the beginnning of each id field and let the letter(s) determine the table. — HLGEM, Jun 25 '15 at 18:07

score 2 · Answer 1 · answered Jun 25 '15 at 18:51

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.

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

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

edited May 23 '17 at 12:06

Community

1
1

answered Jun 25 '15 at 19:26

Zack Stone

643
2
10
23

1

Your code is a little confused about whether a trigger will handle only a single row or multiple rows. The `IF` is problematic if the `INSERT` statement that fires the trigger inserts more than one row. Next you get the `MAX(ID)`. Problematic if rows have been deleted, as you may generate a "new" value that has been seen before. You then apply the "new" id to _all_ of the inserted rows. – HABO Jun 25 '15 at 19:56
Did you see SEQUENCE ? msdn.microsoft.com/en-us/library/ff878091.aspx – Zack Stone Jul 09 '15 at 23:58

score 0 · Answer 3 · answered Jun 25 '15 at 18:14

0

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

answered Jun 25 '15 at 18:14

JamieD77

13,796
1
17
27

Right, but I can't put `2 * 0x1000000000000` as the seed value, that's what I'm trying to achieve here. – Giffyguy Jun 25 '15 at 18:16

T-SQL Identity Seed Expression

3 Answers3