How to change the schema of a table

The other day I set out to performance tune a database deployment. The company was getting ready to implement almost a year’s worth of database schema drift where major restructuring had occurred. One of the first things that I noticed was that they were moving a number of tables into a new schema. This portion of the deployment took over 20 minutes on high performing solid state drives. This was a great opportunity to take advantage of using DDL to make a metadata change instead of moving data.

This is an illustration of what the deployment script was doing originally.

USE AdventureWorks2012;
GO
CREATE SCHEMA HR;
GO

CREATE TABLE [HR].[JobCandidate]
(
	[JobCandidateID] [int] IDENTITY(1,1) NOT NULL,
	[BusinessEntityID] [int] NULL,
	[ResumeXml] [xml](CONTENT [HumanResources].[HRResumeSchemaCollection]) NULL,
	[ModifiedDate] [datetime] NOT NULL 
	CONSTRAINT [DF_JobCandidate_ModifiedDate]  DEFAULT (getdate()),
    CONSTRAINT [PK_JobCandidate_JobCandidateID] 
		PRIMARY KEY CLUSTERED ([JobCandidateID] ASC) 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

SET IDENTITY_INSERT [HR].[JobCandidate] ON
INSERT INTO [HR].[JobCandidate] 
(
	[JobCandidateID]
      ,[BusinessEntityID]
      ,[ResumeXml]
      ,[ModifiedDate]
)
SELECT [JobCandidateID]
      ,[BusinessEntityID]
      ,[Resume]
      ,[ModifiedDate]
FROM [HumanResources].[JobCandidate];
SET IDENTITY_INSERT [HR].[JobCandidate] OFF
GO

DROP TABLE [HumanResources].[JobCandidate];

This method, being used on multiple tables in the database, was causing the 20 minute run time. Here is the correct way to move a table to another schema.

USE AdventureWorks2012;
GO
CREATE SCHEMA HR;
GO
ALTER SCHEMA HR TRANSFER HumanResources.JobCandidate;
GO

There you have it! A sub-second DDL command to replace a lot of code and a lot of disk IO usage. So, we’re done right? Wrong! For those of you who were paying attention, you can see that the original script wasn’t simply switching to a new schema. It also was renaming the Resume column to ResumeXml. Does that mean our faster method won’t work? No, it just means that we’ll need to add one more DDL command.

EXEC sys.sp_rename
	@objname = 'HR.JobCandidate.Resume',
	@newname = 'ResumeXml',
	@objtype = 'COLUMN';

Leave a Reply

%d bloggers like this: