Can we change step value of already existing identity column?

Question

Can we change step value of already existing identity column ? If yes what is the maximum value?

Thanks

Answer 1

Yes, you have to create a copy of the new table, populate it with data from the old table, drop the old one, rename the copy. You can see how this works by changing the step size in the SSMS GUI and seeing what script is produced.

The maximum step size? Based on MSDN, I'd guess as high as your column datatype. So don't use 4000 increment starting at 1 for a tinyint etc

Answer 2

You can do this as a metadata only change. Example code below.

 --Seed Set to 1
CREATE TABLE dbo.tblFoo 
(
id INT IDENTITY(1,1) PRIMARY KEY,
baz INT
)


INSERT INTO dbo.tblFoo
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2


--Change Seed to 7
BEGIN TRY;
    BEGIN TRANSACTION;

    SELECT TOP 0 id 
    FROM dbo.tblFoo
    WITH (TABLOCKX,HOLDLOCK)



    DECLARE @TableScript nvarchar(max)
    SELECT @TableScript = 
    '
    CREATE TABLE dbo.Destination(
        id INT IDENTITY(' + 
                     CAST(ISNULL(IDENT_CURRENT('tblFoo'),0)+1 AS VARCHAR) + ',7)  PRIMARY KEY,
        baz INT
        )

        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
    '       


    EXEC(@TableScript)


    DROP TABLE dbo.tblFoo;

    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';


    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

 INSERT INTO dbo.tblFoo 
 OUTPUT INSERTED.*     
 VALUES (1),(2)

Answer 3

Only by recreating and repopulating the table