Michael J. Swart

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

April 20, 2012

Modifying Tables Online – Part 3: Example With Error Handling

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 1:56 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.

This is part 3 of the series (you can consider this Appendix 2, The main article is found here).

What you find below is exactly the same script as you find in Part 2: Implementation Example with the exception that I’ve included error handling and wrappers to handle the following goals:

  • If a statement fails, no data is lost
  • If a statement fails, other queries are not impacted (i.e. we’re online)
  • If a statement fails anywhere, it can be retried from the beginning with no lost work. This means for example that when data is copied to the staging table in batches, and that process is interrupted and restarted, the script “picks up where it left off”.
  • The process is re-runnable. This means that if the script succeeds and for some reason the script is run again then no errors are raised and no work is performed.
  • The last step, the switch, is an atomic transaction. It succeeds 100% or fails and rolls back 100%.

I present without any further comment, the scripts (with error-handling).

Create Staging Table

use AdventureWorks2012
 
-- original table isn't migrated, staging table doesn't exist
IF NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
BEGIN
 
    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]<=8),
        constraint CK_SalesOrderHeader_new_SubTotal 
            check (SubTotal>=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)
END
GO

Add Triggers

-- staging table exists
-- original table isn't migrated, 
-- triggers don't exist
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
AND NOT EXISTS
    (
        SELECT 1 
        FROM sys.triggers
        WHERE name = 't_i_SalesOrderHeader'
    )
BEGIN
 
    EXEC sp_executesql N'
        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;';
END
GO
 
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
AND NOT EXISTS
    (
        SELECT 1 
        FROM sys.triggers
        WHERE name = 't_u_SalesOrderHeader'
    )
BEGIN
    EXEC sp_executesql N'
        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;';
END
GO
 
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
AND NOT EXISTS
    (
        SELECT 1 
        FROM sys.triggers
        WHERE name = 't_d_SalesOrderHeader'
    )
BEGIN
    EXEC sp_executesql N'
        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 ;';
END
GO

Move the data over in batches

set nocount on;
set transaction isolation level serializable;
set deadlock_priority LOW
 
declare @i int;
declare @new_i int = 0;
declare @largestSalesOrderId int;
declare @minBatchSize int = 5000
 
set identity_insert Sales.SalesOrderHeader_new on;
 
-- largest SalesOrderId to migrate 
select @largestSalesOrderId = isnull(max(SalesOrderID), 0)
from Sales.SalesOrderHeader
 
-- get @i to be just under the lowest SalesOrderId to migrate
select top 1 @i = so.SalesOrderId - 1
from Sales.SalesOrderHeader so
left join Sales.SalesOrderHeader_new so_new
	ON so.SalesOrderId = so_new.SalesOrderId
where so_new.SalesOrderId is null
ORDER BY so.SalesOrderId
 
-- batch loop
while @i < @largestSalesOrderId
begin
 
    declare @counter int = 0;
    declare @error int = 0;
 
    -- retry loop:
    while (1=1)
    begin
        begin try
            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);
 
            --success            
            select @i = @new_i, @error = 0;
            break;
 
        end try
        begin catch
            select @counter    = @counter + 1,
                @error = @@ERROR;
 
            if (@counter >= 5)
            begin
                -- this batch failed five times! Something's wrong
                raiserror (N'Could not copy Sales.SalesOrderHeader rows to Sales.SalesOrderHeader_new',
                    16, 1, @error);
                break;
            end
        end catch
    end 
 
    if @error <> 0
    begin
        -- an error has been raised. Discontinue.
        break;
    end
 
end
 
set identity_insert Sales.SalesOrderHeader_new off;
go

Do The Switch

set xact_abort on
set deadlock_priority high
set transaction isolation level serializable
begin transaction
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
BEGIN
    begin try
        -- 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 (use sp_executesql in order to keep in transaction)
        exec sp_executesql @triggersql;
    end try
 
    begin catch
        declare @error nvarchar(max) = ERROR_MESSAGE();
        select 'An error was encountered... Rolling back' as [message],
            @error as errorMessage
        raiserror (@error, 16, 1)
        rollback
    end catch
END
if @@TRANCOUNT > 0
    commit
GO

3 Comments »

  1. Great writeup!

    One question/suggestion, have you considered using a new schema (e.g. Sales_New) and creating the new table in that schema? The benefit is that all the defaults, keys, triggers, and indexes can retain the same names as the original, and the switch becomes a single ALTER SCHEMA…TRANSFER operation. (or two, one for the old table and one for the new)

    Comment by Rob Volk — April 24, 2012 @ 7:21 am

  2. Excellent comment Rob! In fact I did think of using a new schema. But I thought of it too late and the article was already published. It’s definitely the simpler way to go (and simpler is better).
    Maybe I’ll add an extra part onto this series explaining that strategy.

    Comment by Michael J. Swart — April 24, 2012 @ 7:40 am

  3. […] Modifying Tables Online – Part 3: Example With Error Handling – Here’s Part 3 of this excellent series from Michael J. Swart (Blog|Twitter). […]

    Pingback by Something for the Weekend – SQL Server Links 27/04/12 - John Sansom SQL Server DBA — April 27, 2012 @ 6:03 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress