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,
CONSTRAINT [PK_Shift_New_ShiftID] PRIMARY KEY CLUSTERED
(
[ShiftID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
ALTER TABLE [HumanResources].[Shift_New] ADD  CONSTRAINT [DF_Shift_New_ModifiedDate]  
DEFAULT (getdate()) FOR [ModifiedDate];
GO
-- 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'
GO

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',
@level2name=N'FK_EmployeeDepartmentHistory_Shift_ShiftID';
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]);
GO
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]
CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID];
GO
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',
@level2name=N'FK_EmployeeDepartmentHistory_Shift_ShiftID';
GO

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.

Filed under: Microsoft SQL Server, Partitioning, SQLServerPedia Syndication, T-SQL Tagged: Identity columns, microsoft sql server, Partitioning, T-SQL