{"id":91,"date":"2014-11-19T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/how-to-change-the-schema-of-a-table"},"modified":"2026-03-18T21:51:24","modified_gmt":"2026-03-18T21:51:24","slug":"how-to-change-the-schema-of-a-table","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/11\/19\/how-to-change-the-schema-of-a-table\/","title":{"rendered":"How to change the schema of a table"},"content":{"rendered":"

The other day I set out to performance tune a database deployment. The company was getting ready to implement almost a year\u2019s 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\u00a0to take advantage of using DDL to make a metadata change instead of moving data.<\/p>\n

This is an illustration of what the deployment script was doing originally.<\/p>\n

USE AdventureWorks2012;\nGO\nCREATE SCHEMA HR;\nGO\n\n
CREATE TABLE [HR].[JobCandidate]\n(\n\t[JobCandidateID] [int] IDENTITY(1,1) NOT NULL,\n\t[BusinessEntityID] [int] NULL,\n\t[ResumeXml] 1(CONTENT [HumanResources].[HRResumeSchemaCollection]) NULL,\n\t[ModifiedDate] [datetime] NOT NULL \n\tCONSTRAINT [DF_JobCandidate_ModifiedDate]  DEFAULT (getdate()),\n    CONSTRAINT [PK_JobCandidate_JobCandidateID] \n\t\tPRIMARY KEY CLUSTERED ([JobCandidateID] ASC) \n) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];\nGO<\/p>\n\n
SET IDENTITY_INSERT [HR].[JobCandidate] ON\nINSERT INTO [HR].[JobCandidate] \n(\n\t[JobCandidateID]\n      ,[BusinessEntityID]\n      ,[ResumeXml]\n      ,[ModifiedDate]\n)\nSELECT [JobCandidateID]\n      ,[BusinessEntityID]\n      ,[Resume]\n      ,[ModifiedDate]\nFROM [HumanResources].[JobCandidate];\nSET IDENTITY_INSERT [HR].[JobCandidate] OFF\nGO<\/p>\n\n
DROP TABLE [HumanResources].[JobCandidate];<\/code><\/pre>\n<\/p>\n
This method, being used on multiple tables in the database, was causing the 20 minute run time. Here\u00a0is the correct way to move a table to another schema.<\/p>\n
USE AdventureWorks2012;\nGO\nCREATE SCHEMA HR;\nGO\nALTER SCHEMA HR TRANSFER HumanResources.JobCandidate;\nGO<\/code><\/pre>\n
There you have it! A sub-second DDL command to replace a lot of code and a lot of disk IO usage. So, we\u2019re done right? Wrong! For those of you who were paying attention, you can see that the original script wasn\u2019t simply switching to a new schema. It also was renaming the Resume column to ResumeXml. Does that mean our faster method won\u2019t work? No, it just means that we\u2019ll need to add one more DDL command.<\/p>\n
EXEC sys.sp_rename\n\t@objname = 'HR.JobCandidate.Resume',\n\t@newname = 'ResumeXml',\n\t@objtype = 'COLUMN';<\/code><\/pre>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
The other day I set out to performance tune a database deployment. The company was getting ready to implement almost a year\u2019s 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 […]<\/p>\n","protected":false},"author":1,"featured_media":530,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-91","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/91","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=91"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/91\/revisions"}],"predecessor-version":[{"id":354,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/91\/revisions\/354"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/530"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=91"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=91"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=91"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}