SQL Server Identity Seed – Configure Starting Value

identitysql server

Is there an option in sql server to configure the default starting value(seed) of an identity column on a database level .

I want that by default, every table that created with an identity column ,the first value will start from 3000 . And not from 1 .
I know how to do this in a table level. But is it possible in a database level?

10x

Best Answer

You could use a DDL trigger to reseed the identity value in all newly created table with an identity column to start at 3000. 

ALTER TRIGGER [ident] ON DATABASE
FOR CREATE_TABLE
AS
    BEGIN
        DECLARE @EventData  xml            = EVENTDATA()
            , @tablename  sysname
            , @sql        nvarchar(1000)
            , @isidentity bit;

        SELECT @tablename = @EventData.value
             ('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

        SELECT @isidentity = 1
        FROM [sys].[tables] AS [t]
        INNER JOIN [sys].[columns] AS [c]
        ON [t].object_id = [c].object_id
        WHERE [c].[is_identity] = 1
            AND [t].[name] = @tablename;

        IF @isidentity = 1
           BEGIN
              DBCC CHECKIDENT(@tablename, RESEED, 3000);
        END;
    END;
GO
--create table with identity
CREATE TABLE [testidentity]([id] int IDENTITY(1, 1));

INSERT INTO [testidentity]
DEFAULT VALUES;

SELECT *
FROM [testidentity];

--create table without identity
CREATE TABLE [testnoidentity]([id]);
Related Question