Mainting Identity value across multiple tables

Question

We have a situation where we have a column called Customer_Number in multiple tables. This column is identity column in all the tables, but is there a way that I can make this column unique among all the tables.

for example if I add a row in table_one and identity column assigns it value 1 now if someone add another row in Customer_Number column of table_two , table_three or table_four it should be assigned 2.

how can I do this, I have been reading online and it seems I have to create a table to keep record of the last generated value for any of the table and get MAX() of values from that table and add 1 to it to get the next available value, is there a simpler way of doing this?

Answer 1

I have not used it myself but I think you need the new Sequence Object

You would Create a Sequence Object and rather then using Identity values just get the next value from your sequence object.

Create Sequence Object

CREATE SEQUENCE Sqnc_Number_Generator AS INT   --<-- This can be Bigint as well
    START WITH   1  -- Start with value 1
    INCREMENT BY 1  -- Increment with value 1
    MINVALUE  1     -- Minimum value to start is 1
    MAXVALUE  50000 -- Maximum it can go to 5000
    NO CYCLE        -- Do not go above 5000
    CACHE 500        -- Increment 500 values in memory rather than incrementing from IO

Getting Next value

SELECT NEXT VALUE FOR dbo.Sqnc_Number_Generator AS NxtValue;

SQL FIDDLE