0

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

0

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]);
6

No it is not possible at a database level.

IDENTITY is not a data type, It is a table's column definition.

  • 1
    Yes. I thought that'll be the answer. I think maybe the solution is to create ddl trigger. 10x anyway. – itzik Paz Jul 1 '17 at 10:30
  • You are welcome, do you mind accept this as an answer so other user can be benefited in future. – SqlWorldWide Jul 1 '17 at 13:18
  • Let's give it another shot for a couple of days. And then . 10x – itzik Paz Jul 1 '17 at 13:20
0

I wondered if you can have a user-defined data type in tables which is always an identity column with the settings that you want. Then you would make developers use that data type in tables. I looked at this document but I don't understand what is possible, sorry.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql

After tables are created, you can examine the identity column information of one table at a time by using the "DBCC CHECKIDENT" operation.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

For this I'd write a script that generates the "DBCC" command as text output for each table that has an identity column. Something like:

SELECT N'DBCC CHECKDB([' + TABLE_NAME + '], NORESEED)' 
FROM where_table_names_come_from

Then you need something to look at the result and find unsatisfactory values.

You can vary the command, to override and set the value used in the identity column in new rows, but you aren't prevented from setting it to a value that already exists in a row in the table, i.e. producing a duplicate key. That can be avoided by only making that change when the table has no rows. Fixing data that was already input without respecting your condition may need to be a separate exercise, anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.