How do I change the increment of an identity column?


April 5, 2017 by Kenneth Fisher

Wellll .. technically there is no native way to do it. I even went through my notes on identity columns. No luck.

So how do we do it then? Well, believe it or not, there is a way. The other day Adam Machanic (b/t) pointed out that you can do it using a partitioning trick.

The first thing to do is remember that all tables are partitioned. Sort of. What we think of as non-partitioned tables are really just tables with a single partition. Every table is listed in sys.partitions and in fact you can use it to quickly see how many rows there are in a table. Since there is no partition scheme/function we can’t do splits or merges, but we can do a SWITCH.

What we are going to do is create a new, virtually identical table, then switch the data from the old table (partition) to the new table (partition). The trick is that while in order to do the switch almost everything has to be identical, the properties of the identity column are part of that almost.

Now my demo here is fairly long and has a lot of extras. I left all that in so you can see some complexity. I also deliberately used a table that is the parent of a foreign key. You can’t do a SWITCH on a table that is the parent of a foreign key so that piece has to be dropped and re-create. Changing the increment on this type of table is probably going to be fairly unusual (even more than doing it on any table) but I thought I would add it in just in case someone comes across it.

First we create the new table

-- Get the next identity value to use
-- as the new seed.
SELECT IDENT_CURRENT('HumanResources.Shift') + 1;

-- Script out the table and modify the script to have a new table name
-- Make sure you not only modify the table name and all references to it
-- but things like indexes and constraints as well.
CREATE TABLE [HumanResources].[Shift_New](
	[ShiftID] [tinyint] IDENTITY(4 /* from IDENT_CURRENT above */,
								2 /* The new increment */) NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[StartTime] [time](7) NOT NULL,
	[EndTime] [time](7) NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[ShiftID] ASC

ALTER TABLE [HumanResources].[Shift_New] ADD  CONSTRAINT [DF_Shift_New_ModifiedDate]  
		DEFAULT (getdate()) FOR [ModifiedDate];

-- These aren't necessary for the SWAP but
--  we still want them on the new table.
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for Shift records.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'COLUMN',@level2name=N'ShiftID';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Shift description.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'COLUMN',@level2name=N'Name';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Shift start time.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'COLUMN',@level2name=N'StartTime';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Shift end time.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'COLUMN',@level2name=N'EndTime';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'COLUMN',@level2name=N'ModifiedDate';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'CONSTRAINT',@level2name=N'DF_Shift_New_ModifiedDate';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New', @level2type=N'CONSTRAINT',@level2name=N'PK_Shift_New_ShiftID';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Work shift lookup table.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Shift_New'

Swap the data and clean up

-- Unfortunately we can't SWITCH if the table is the parent of a FK relationship
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' ,
	@level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',
	@level1name=N'EmployeeDepartmentHistory', @level2type=N'CONSTRAINT',

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]
	DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID];

-- Perform the SWITCH
ALTER TABLE [HumanResources].[Shift] SWITCH TO [HumanResources].[Shift_New];

-- Drop the now empty table
DROP TABLE [HumanResources].[Shift];

-- Rename all of the new objects to the old names.
EXEC sp_rename 'HumanResources.Shift_New','Shift';
EXEC sp_rename 'HumanResources.PK_Shift_New_ShiftID','PK_Shift_ShiftID';
EXEC sp_rename 'HumanResources.DF_Shift_New_ModifiedDate','DF_Shift_ModifiedDate';

-- Now we have to put the FK back.
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD
	CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY([ShiftID])
	REFERENCES [HumanResources].[Shift] ([ShiftID]);
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]
	CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID];

EXEC sys.sp_addextendedproperty @name=N'MS_Description',
	@value=N'Foreign key constraint referencing Shift.ShiftID' ,
	@level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',
	@level1name=N'EmployeeDepartmentHistory', @level2type=N'CONSTRAINT',

Remember that a SWITCH is a metadata operation so it’s fast even on really large tables. The only slow part here is re-creating the foreign key and that’s going to be somewhat unusual. Even with that, however, it’s going to be faster than any other method I can think of to modify the increment.

7 thoughts on “How do I change the increment of an identity column?

  1. […] is a really handy, if rather complex tool. It can be used for a lot of odd tasks but one of its primary uses is to move data in and out of a table quickly. We do that using a […]

  2. […] I’ve got a way to do it right? Or this would be a really pointless post. Turns out, yet again that the identity property is not one of those things that needs to be identical when doing a […]

  3. […] Now we have a million rows and nowhere to call home. Let’s create a new table with an identity column and the same schema otherwise (including indexes, key constraints, and everything else). For more notes on this, check out Kenneth’s other blog post on changing identity column increments. […]

  4. m60freeman says:

    How is this working without a DBCC CHECKIDENT?

    • Not sure I understand what you mean?

      • m60freeman says:

        I see that you are doing this: SELECT IDENT_CURRENT(‘HumanResources.Shift’) + 1;

        So it displays the value but is not storing it to a variable. I don’t see you doing a RESEED of the IDENTITY after the SWITCH. Is that not needed in this case?

        • Took me a sec to see what I’d done myself. If you look at line 9 I just ran the SELECT, got the result and put it into the query manually. No variable. And no, no need for a reseed since I started the seed at 1 higher than the max existing value. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: