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; |
-- 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 |
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 |
/*
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; |
/*
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; |
-- 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;