Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

April 17, 2012

Modifying Tables Online – Part 2: Implementation Example

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 12:00 pm

SERIES: Modifying Tables Online

In the next few days I want to describe a way to modify the definition of very large tables while keeping the table available for other queries. Some table changes are already online operations. But offline changes need extra work to keep the table available. I explore one way to do that in this series.

This article used to be a single blog post, but I broke it into four parts because it seemed too long. Read Part 1, and understand the strategy I’m using. Treat Parts 2-4 as appendixes. When you want to use this plan (or one like it), come back and use them as examples. You won’t be able to make use of the examples by cutting and pasting directly (unless you happen to be running an bike equipment store called Adventureworks), but modifications shouldn’t be too hard.

So this is me putting the strategy into practice. I’ve decided to migrate a table from everyone’s favorite sample datatabase Adventureworks (2012 edition). And the change that I’m implementing is the addition of a rowversion column which is considered an offline operation:

-- adding a rowversion column offline
ALTER TABLE Sales.SalesOrderHeader
ADD [rowversion] rowversion;

The table I picked is Sales.SalesOrderHeader. I picked this table because it’s got foreign keys (columns pointing to records in other tables). And other tables have foreign keys pointing to SalesOrderHeader too. I also noticed that this table has a large variety of objects and settings which are dependent on it. Besides the foreign keys, other dependencies include

  • triggers
  • indexes (some UNIQUE and some not, some nonclustered and one not)
  • an identity column
  • default constraints
  • check constraints
  • computed columns

These make Sales.SalesOrderHeader interesting. So here’s the script (an implementation of the strategy already mentioned). You might be surprised at its size, it’s directly related to how interesting the table is. I don’t use any 2012-only features so you’ll be able to use these examples on SQL Server 2008 or SQL Server 2008 R2.

Create Staging Table

use AdventureWorks2012
 
-- new table
CREATE TABLE Sales.SalesOrderHeader_new(
	SalesOrderID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	RevisionNumber tinyint NOT NULL,
	OrderDate datetime NOT NULL,
	DueDate datetime NOT NULL,
	ShipDate datetime NULL,
	[Status] tinyint NOT NULL,
	OnlineOrderFlag dbo.Flag NOT NULL,
	SalesOrderNumber  AS (isnull(N'SO'+CONVERT(nvarchar(23),SalesOrderID),N'*** ERROR ***')),
	PurchaseOrderNumber dbo.OrderNumber NULL,
	AccountNumber dbo.AccountNumber NULL,
	CustomerID int NOT NULL,
	SalesPersonID int NULL,
	TerritoryID int NULL,
	BillToAddressID int NOT NULL,
	ShipToAddressID int NOT NULL,
	ShipMethodID int NOT NULL,
	CreditCardID int NULL,
	CreditCardApprovalCode varchar(15) NULL,
	CurrencyRateID int NULL,
	SubTotal money NOT NULL,
	TaxAmt money NOT NULL,
	Freight money NOT NULL,
	TotalDue  AS (isnull((SubTotal+TaxAmt)+Freight,(0))),
	Comment nvarchar(128) NULL,
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
	[Rowversion] rowversion NOT NULL, -- This is the new column!!
	constraint PK_SalesOrderHeader_new_SalesOrderID
		primary key clustered (SalesOrderID ASC),
	constraint FK_SalesOrderHeader_new_Address_BillToAddressID
		foreign key(BillToAddressID) references Person.[Address] (AddressID),
	constraint FK_SalesOrderHeader_new_Address_ShipToAddressID
		foreign key(ShipToAddressID) references Person.[Address] (AddressID),
	constraint FK_SalesOrderHeader_new_CreditCard_CreditCardID
		foreign key(CreditCardID) references Sales.CreditCard (CreditCardID),
	constraint FK_SalesOrderHeader_new_CurrencyRate_CurrencyRateID
		foreign key(CurrencyRateID) references Sales.CurrencyRate (CurrencyRateID),
	constraint FK_SalesOrderHeader_new_Customer_CustomerID
		foreign key(CustomerID) references Sales.Customer (CustomerID),
	constraint FK_SalesOrderHeader_new_SalesPerson_SalesPersonID
		foreign key(SalesPersonID) references Sales.SalesPerson (BusinessEntityID),
	constraint FK_SalesOrderHeader_new_SalesTerritory_TerritoryID
		foreign key(TerritoryID) references Sales.SalesTerritory (TerritoryID),
	constraint FK_SalesOrderHeader_new_ShipMethod_ShipMethodID
		foreign key(ShipMethodID) references Purchasing.ShipMethod (ShipMethodID),
	constraint CK_SalesOrderHeader_new_DueDate
		check (DueDate>=OrderDate),
	constraint CK_SalesOrderHeader_new_Freight
		check (Freight>=0.00),
	constraint CK_SalesOrderHeader_new_ShipDate
		check (ShipDate>=OrderDate or ShipDate is null),
	constraint CK_SalesOrderHeader_new_Status
		check ([Status]>=0 and [Status]=0.00),
	constraint CK_SalesOrderHeader_new_TaxAmt
		check (TaxAmt>=0.00)
)
 
-- add default constraints
alter table Sales.SalesOrderHeader_new add
constraint DF_SalesOrderHeader_new_RevisionNumber
	default 0 for RevisionNumber,
constraint DF_SalesOrderHeader_new_OrderDate
	default getdate() for OrderDate,
constraint DF_SalesOrderHeader_new_Status
	default 1 for [Status],
constraint DF_SalesOrderHeader_new_OnlineOrderFlag
	default 1 for OnlineOrderFlag,
constraint DF_SalesOrderHeader_new_SubTotal
	default 0.00 for SubTotal,
constraint DF_SalesOrderHeader_new_TaxAmt
	default 0.00 for TaxAmt,
constraint DF_SalesOrderHeader_new_Freight
	default 0.00 for Freight,
constraint DF_SalesOrderHeader_new_rowguid
	default newid() for rowguid,
constraint DF_SalesOrderHeader_new_ModifiedDate
	default getdate() for ModifiedDate
 
	-- two nc ak
create unique nonclustered index AK_SalesOrderHeader_new_rowguid
	on Sales.SalesOrderHeader_new(rowguid ASC)
 
create unique nonclustered index AK_SalesOrderHeader_new_SalesOrderNumber
	on Sales.SalesOrderHeader_new(SalesOrderNumber)
 
	-- two nc ix
create nonclustered index IX_SalesOrderHeader_new_CustomerID
	on Sales.SalesOrderHeader_new(CustomerID)
 
create nonclustered index IX_SalesOrderHeader_new_SalesPersonID
	on Sales.SalesOrderHeader_new(SalesPersonID)
GO

Add Triggers

/*
create triggers to maintain new table
*/
 
use AdventureWorks2012;
go
 
create trigger t_i_SalesOrderHeader
on Sales.SalesOrderHeader
after insert
as
	set identity_insert Sales.SalesOrderHeader_new on;
 
	insert SalesOrderHeader_new(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
	select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
	from inserted
 
	set identity_insert Sales.SalesOrderHeader_new off;
go
 
create trigger t_u_SalesOrderHeader
on Sales.SalesOrderHeader
after update
as
	-- assuming pk values are never altered.
	update Sales.SalesOrderHeader_new
	set
		RevisionNumber = i.RevisionNumber,
		OrderDate  = i.OrderDate,
		DueDate  = i.DueDate,
		ShipDate  = i.ShipDate,
		[Status]  = i.[Status],
		OnlineOrderFlag  = i.OnlineOrderFlag,
		PurchaseOrderNumber  = i.PurchaseOrderNumber,
		AccountNumber  = i.AccountNumber,
		CustomerID  = i.CustomerID,
		SalesPersonID  = i.SalesPersonID,
		TerritoryID  = i.TerritoryID,
		BillToAddressID  = i.BillToAddressID,
		ShipToAddressID  = i.ShipToAddressID,
		ShipMethodID  = i.ShipMethodID,
		CreditCardID  = i.CreditCardID,
		CreditCardApprovalCode = i.CreditCardApprovalCode,
		CurrencyRateID  = i.CurrencyRateID,
		SubTotal  = i.SubTotal,
		TaxAmt  = i.TaxAmt,
		Freight  = i.Freight,
		Comment  = i.Comment,
		rowguid  = i.rowguid,
		ModifiedDate = i.ModifiedDate
	from Sales.SalesOrderHeader_new s
	join inserted i
		on s.SalesOrderID = i.SalesOrderID;
go
 
create trigger t_d_SalesOrderHeader
on Sales.SalesOrderHeader
after delete
as
	delete Sales.SalesOrderHeader_new
	from Sales.SalesOrderHeader_new s
	join deleted d
		on d.SalesOrderID = s.SalesOrderID ;
go

Move the data over in batches

/* 
populate new table in batches
*/
set nocount on;
set transaction isolation level serializable;
 
declare @i int = 0;
declare @new_i int = 0;
declare @rowcount int = -1;
declare @minBatchSize int = 5000
 
set identity_insert Sales.SalesOrderHeader_new on;
 
while @rowcount <> 0
begin
	select top (@minBatchSize) @new_i = SalesOrderID
	from Sales.SalesOrderHeader
	where SalesOrderID > @i
	order by SalesOrderID asc;
 
	merge Sales.SalesOrderHeader_new as target
	using (
			select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
			from Sales.SalesOrderHeader
			where SalesOrderID > @i
			and SalesOrderID <= @new_i
		) as source (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
	on target.SalesOrderID = source.SalesOrderID
	when not matched then
		insert (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
		values (source.SalesOrderID, source.RevisionNumber, source.OrderDate, source.DueDate, source.ShipDate, source.[Status], source.OnlineOrderFlag, source.PurchaseOrderNumber, source.AccountNumber, source.CustomerID, source.SalesPersonID, source.TerritoryID, source.BillToAddressID, source.ShipToAddressID, source.ShipMethodID, source.CreditCardID, source.CreditCardApprovalCode, source.CurrencyRateID, source.SubTotal, source.TaxAmt, source.Freight, source.Comment, source.rowguid, source.ModifiedDate);
 
	set @rowcount = @@ROWCOUNT
 
	set @i = @new_i
 
end
 
set identity_insert Sales.SalesOrderHeader_new off;

Do The Switch

-- rename pks and indexes from original table
exec sp_rename N'Sales.PK_SalesOrderHeader_SalesOrderID', N'PK_SalesOrderHeader_old_SalesOrderID', N'OBJECT';
exec sp_rename N'Sales.SalesOrderHeader.AK_SalesOrderHeader_rowguid', N'AK_SalesOrderHeader_old_rowguid', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader.AK_SalesOrderHeader_SalesOrderNumber', N'AK_SalesOrderHeader_old_SalesOrderNumber', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID', N'IX_SalesOrderHeader_old_CustomerID', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader.IX_SalesOrderHeader_SalesPersonID', N'IX_SalesOrderHeader_old_SalesPersonID', N'INDEX'
 
-- rename checks and defaults
exec sp_rename N'Sales.CK_SalesOrderHeader_Status', 'CK_SalesOrderHeader_old_Status', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_DueDate', 'CK_SalesOrderHeader_old_DueDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_ShipDate', 'CK_SalesOrderHeader_old_ShipDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_SubTotal', 'CK_SalesOrderHeader_old_SubTotal', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_TaxAmt', 'CK_SalesOrderHeader_old_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_Freight', 'CK_SalesOrderHeader_old_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_RevisionNumber', 'DF_SalesOrderHeader_old_RevisionNumber', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_OrderDate', 'DF_SalesOrderHeader_old_OrderDate', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_Status', 'DF_SalesOrderHeader_old_Status', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_OnlineOrderFlag', 'DF_SalesOrderHeader_old_OnlineOrderFlag', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_SubTotal', 'DF_SalesOrderHeader_old_SubTotal', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_TaxAmt', 'DF_SalesOrderHeader_old_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_Freight', 'DF_SalesOrderHeader_old_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_rowguid', 'DF_SalesOrderHeader_old_rowguid', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_ModifiedDate', 'DF_SalesOrderHeader_old_ModifiedDate', N'OBJECT';
 
-- drop fks from original table
alter table Sales.SalesOrderHeader
	drop constraint
	FK_SalesOrderHeader_Address_BillToAddressID,
	FK_SalesOrderHeader_Address_ShipToAddressID,
	FK_SalesOrderHeader_CreditCard_CreditCardID,
	FK_SalesOrderHeader_CurrencyRate_CurrencyRateID,
	FK_SalesOrderHeader_Customer_CustomerID,
	FK_SalesOrderHeader_SalesPerson_SalesPersonID,
	FK_SalesOrderHeader_ShipMethod_ShipMethodID,
	FK_SalesOrderHeader_SalesTerritory_TerritoryID
 
-- drop fks to original table
alter table Sales.SalesOrderDetail
	drop constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID;
 
alter table Sales.SalesOrderHeaderSalesReason
	drop constraint FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID;
 
-- drop triggers on original table
drop trigger Sales.t_i_SalesOrderHeader;
drop trigger Sales.t_u_SalesOrderHeader;
drop trigger Sales.t_d_SalesOrderHeader;
 
-- drop uSalesOrderHeader (but remember its definition)
declare @triggersql nvarchar(max);
select @triggersql = OBJECT_DEFINITION(object_id) from sys.triggers where name = 'uSalesOrderHeader';
drop trigger Sales.uSalesOrderHeader; 
 
-- rename original table to "old"
exec sp_rename N'Sales.SalesOrderHeader', N'SalesOrderHeader_old', N'OBJECT';
 
--rename the new indexes
exec sp_rename N'Sales.PK_SalesOrderHeader_new_SalesOrderID', N'PK_SalesOrderHeader_SalesOrderID', N'OBJECT';
exec sp_rename N'Sales.SalesOrderHeader_new.AK_SalesOrderHeader_new_rowguid', N'AK_SalesOrderHeader_rowguid', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader_new.AK_SalesOrderHeader_new_SalesOrderNumber', N'AK_SalesOrderHeader_SalesOrderNumber', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader_new.IX_SalesOrderHeader_new_CustomerID', N'IX_SalesOrderHeader_CustomerID', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader_new.IX_SalesOrderHeader_new_SalesPersonID', N'IX_SalesOrderHeader_SalesPersonID', N'INDEX'
 
-- rename the new checks and defaults
exec sp_rename N'Sales.CK_SalesOrderHeader_new_Status', 'CK_SalesOrderHeader_Status', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_DueDate', 'CK_SalesOrderHeader_DueDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_ShipDate', 'CK_SalesOrderHeader_ShipDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_SubTotal', 'CK_SalesOrderHeader_SubTotal', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_TaxAmt', 'CK_SalesOrderHeader_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_Freight', 'CK_SalesOrderHeader_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_RevisionNumber', 'DF_SalesOrderHeader_RevisionNumber', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_OrderDate', 'DF_SalesOrderHeader_OrderDate', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_Status', 'DF_SalesOrderHeader_Status', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_OnlineOrderFlag', 'DF_SalesOrderHeader_OnlineOrderFlag', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_SubTotal', 'DF_SalesOrderHeader_SubTotal', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_TaxAmt', 'DF_SalesOrderHeader_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_Freight', 'DF_SalesOrderHeader_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_rowguid', 'DF_SalesOrderHeader_rowguid', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_ModifiedDate', 'DF_SalesOrderHeader_ModifiedDate', N'OBJECT';
 
-- rename "new" table
exec sp_rename N'Sales.SalesOrderHeader_new', N'SalesOrderHeader', N'OBJECT';
 
--rename fks pointing out
exec sp_rename N'Sales.FK_SalesOrderHeader_new_Address_BillToAddressID', N'FK_SalesOrderHeader_Address_BillToAddressID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_Address_ShipToAddressID', N'FK_SalesOrderHeader_Address_ShipToAddressID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_CreditCard_CreditCardID', N'FK_SalesOrderHeader_CreditCard_CreditCardID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_CurrencyRate_CurrencyRateID', N'FK_SalesOrderHeader_CurrencyRate_CurrencyRateID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_Customer_CustomerID', N'FK_SalesOrderHeader_Customer_CustomerID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_SalesPerson_SalesPersonID', N'FK_SalesOrderHeader_SalesPerson_SalesPersonID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_ShipMethod_ShipMethodID', N'FK_SalesOrderHeader_ShipMethod_ShipMethodID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_SalesTerritory_TerritoryID', N'FK_SalesOrderHeader_SalesTerritory_TerritoryID', N'OBJECT'
 
-- re-add fks pointing in (without checks for now)
alter table Sales.SalesOrderDetail with nocheck add
constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID foreign key (SalesOrderId)
	references Sales.SalesOrderHeader(SalesOrderId) 
 
alter table Sales.SalesOrderHeaderSalesReason with nocheck add
constraint FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID foreign key (SalesOrderId)
	references Sales.SalesOrderHeader(SalesOrderId)
 
-- re-add trigger
exec sp_executesql @triggersql;

3 Comments »

  1. I’m curious . . . can your “switch” be wrapped in a transaction? If it can be wrapped and you don’t isn’t there the potential (however brief) for failure between the renames of the original to old and the new to original?

    If you can’t wrap it in a transaction with rollback capabilities then that risk exists regardless.

    One other question, have you considered generating the “switch” code by querying the metadata tables rather than hardcoding all names? As long as you own your environment I suppose there is no need for the additional complexity but in an environment where the names of objects may be changed could you discover all of the dependencies through the SQL Meta data?

    Anyway, great post.

    Thanks,

    Matt

    Comment by Matt O — April 17, 2012 @ 3:03 pm

  2. You bet Matt!
    Transactions and conditional executions. That’s part 3. Stay tuned.

    About the meta data stuff, yes, you probably could write something more generic and robust based on meta-data. That’s a great idea for a codeplex project. If you’re up for it, I’d probably contribute where I could.

    Comment by Michael J. Swart — April 17, 2012 @ 3:08 pm

  3. […] Modifying Tables Online – Part 2: Implementation Example – Continuing his excellent series, it’s Michael J. Swart (Blog|Twitter) […]

    Pingback by Something for the Weekend - SQL Server Links 20/04/12 — May 27, 2012 @ 6:14 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress