Change Auto-Increment, Primary Key field from SMALLINT to INT in SQL Server

Question

What is the best way (low impact/low risk) to change a primary key field from SMALLINT to INT? The field is configured to use "Identity Increment" to auto-increment.

I'm starting with the following SQL:

ALTER TABLE category_types ALTER COLUMN id INT NOT NULL;

However, it generates the following error:

ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.

What else is required? Do I need to drop the keys then recreate them? Will this impact the auto-incrementation?

Note: the table doesn't have too many rows, so the performance of the solution is not critical.

Answer 1

This is output from sql server management studio but should be of some help. It involves creating a temporary table and then copying existing data to the new table. Dropping the original table and renaming the temp table. If the table was empty, it wouldn't do this and could just modify the original table. SET IDENTITY_INSERT [table] ON/OFF allows you to set the identity column when inserting.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_category_types
    (
    id int NOT NULL IDENTITY (1, 1),
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_category_types SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_category_types ON
GO
IF EXISTS(SELECT * FROM dbo.category_types)
     EXEC('INSERT INTO dbo.Tmp_category_types (id, x)
        SELECT CONVERT(int, id), x FROM dbo.category_types WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_category_types OFF
GO
DROP TABLE dbo.category_types
GO
EXECUTE sp_rename N'dbo.Tmp_category_types', N'category_types', 'OBJECT' 
GO
ALTER TABLE dbo.category_types ADD CONSTRAINT
    PK_category_types PRIMARY KEY CLUSTERED 
    (
    id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Answer 2

Besides setting IDENTITY_INSERT to off, you have to unlink relationships (primary key, foreign keys) before changing data types. Afterwards you have to link them again.

For instance:

-- Change from smallint to int
SET IDENTITY_INSERT category_types OFF
alter table category_types drop CONSTRAINT CategoryTypesPK
alter table category_types alter column id int
alter table category_types add CONSTRAINT CategoryTypesPK PRIMARY KEY (id)
SET IDENTITY_INSERT category_types ON

Answer 3

I realise this is an old post, but just in case someone stumbles on it: jciberta gave an answer with a slight error. it should read:

-- Change from smallint to int

SET IDENTITY_INSERT category_types ON

ALTER TABLE category_types DROP CONSTRAINT CategoryTypes

ALTER TABLE category_types ALTER COLUMN id INT

ALTER TABLE category_types ADD CONSTRAINT CategoryTypesPK PRIMARY KEY (id)

SET IDENTITY_INSERT category_types OFF