Michael J. Swart

The View of Foreign Keys That I Wanted

Michael J. Swart — Thu, 24 May 2012 00:47:47 +0000

So I’ve been working on an automation project that makes frequent use of foreign key metadata. I find myself writing queries for this data but I discovered that there’s no super-easy out-of-the-box view of foreign keys for me to use. Here are the ones I considered.

INFORMATION_SCHEMA views

INFORMATION_SCHEMA views that give me foreign key information include

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the foreign key info
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for the referring columns,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the referenced columns

The first view here gives a list of foreign keys and you have to join to the other two tables in order to find the column names. But it’s a crummy solution. First of all, if the foreign key has multiple columns, there’s no real way to match the referring columns to the referenced columns.

The second thing is that we don’t see foreign keys that point to unique Keys (as pointed out by Aaron Bertrand in his post The case against INFORMATION_SCHEMA views.

So that’s out. What else have we got in?

Microsoft SQL Server system views

These views include

sys.foreign_keys
sys.foreign_key_columns
with support from sys.columns and sys.tables

These are the views I deserve, but not the views I need right now. The joins are just too annoying to remember and type each time.

Besides, the word “parent” used here changes with context. The parent table in a foreign key relationship owns the foreign key and does the pointing. But say I’m modeling a hierarchy. In the context of the data model, children records point to their parent records. The mental effort needed to keep these straight is not difficult, but it’s annoying.

My Own Views

So I’ve created my own, the goal is to simplify typing and minimize joins. I skip the word “parent” all together and use “referrer” and “referrenced”. Feel free to use and build on these.

FOREIGN_KEYS

CREATE VIEW dbo.FOREIGN_KEYS
AS
SELECT  SCHEMA_NAME(fk.schema_id) AS FKSchema ,
        fk.name AS FK ,
        SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
        p.name AS Referrer ,
        STUFF(CAST(( SELECT ',' + c.name
                     FROM   sys.foreign_key_columns fkc
                            JOIN sys.columns c ON fkc.parent_object_id = c.object_id
                                                  AND fkc.parent_column_id = c.column_id
                     WHERE  fkc.constraint_object_id = fk.object_id
                     ORDER BY fkc.constraint_column_id ASC
                   FOR
                     XML PATH('') ,
                         TYPE
                   ) AS NVARCHAR(MAX)), 1, 1, '') AS ReferrerColumns ,
        SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
        r.name AS Referenced ,
        STUFF(CAST(( SELECT ',' + c.name
                     FROM   sys.foreign_key_columns fkc
                            JOIN sys.columns c ON fkc.referenced_object_id = c.object_id
                                                  AND fkc.referenced_column_id = c.column_id
                     WHERE  fkc.constraint_object_id = fk.object_id
                     ORDER BY fkc.constraint_column_id ASC
                   FOR
                     XML PATH('') ,
                         TYPE
                   ) AS NVARCHAR(MAX)), 1, 1, '') AS ReferencedColumns ,
        fk.delete_referential_action_desc AS deleteAction ,
        fk.update_referential_action_desc AS updateAction ,
        fk.object_id AS FKId ,
        p.object_id AS ReferrerId ,
        r.object_id AS ReferencedId
FROM    sys.foreign_keys fk
        JOIN sys.tables p ON p.object_id = fk.parent_object_id
        JOIN sys.tables r ON r.object_id = fk.referenced_object_id
GO

FOREIGN_KEY_COLUMNS

CREATE VIEW dbo.FOREIGN_KEY_COLUMNS
AS
SELECT  SCHEMA_NAME(fk.schema_id) AS FKSchema ,
        fk.name AS FK ,
        SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
        p.name AS Referrer ,
        pc.name AS ReferrerColumn ,
        SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
        r.name AS Referenced,
        rc.name AS ReferencedColumn ,
        fk.object_id AS FKId ,
        fkc.constraint_column_id AS FKColumnId ,
        p.object_id AS ReferrerId ,
        r.object_id AS ReferencedId
FROM    sys.foreign_keys fk
        JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        JOIN sys.tables p ON p.object_id = fk.parent_object_id
        JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id
                               AND fkc.parent_column_id = pc.column_id
        JOIN sys.tables r ON r.object_id = fk.referenced_object_id
        JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id
                               AND fkc.referenced_column_id = rc.column_id
GO

Lessons From Geordi La Forge

Michael J. Swart — Mon, 14 May 2012 15:56:23 +0000

I’ve been rewatching Star Trek again, I find I relate most to Geordi La Forge. He’s a technical guy. He wears the yellow shirt of engineering instead of the red shirt of command.

So I was inspired to write this light hearted post. I usually find non-technical posts a little wishy-washy (No offense to you Seth-Godin-types). But I liked the way this drawing turned out and I had to use it somehow right?

You may have already seen articles or websites elsewhere that detail a comparison between Star Trek operations and company operations. Well here’s another one. For the moment we’ll sidestep the lessons learned from Picard (on how to be a inspiring leader) and go straight to Geordi. Here are the lessons:

Make Everyone Believe You Can Do Anything …

Here’s how Geordi’s typically responds to requests from the captain:

either "I can fix that captain. Estimated time two minutes." or "Sorry captain, The flux couplers aren't in phase."" width="344" height="173" />

Did you see what he did there? It’s not a choice between “I can” or “I can’t”. It’s also not a choice between “it’s possible” or “it’s impossible”. If Geordi can accomplish a task, he essentially takes the credit with “I can do this”. If he can’t accomplish the task, then his words imply “It can’t be done.” He never implies that his skills are lacking. So are Geordi’s skills ever lacking? No:

… And Then Make Sure They’re Right …

Even when the pressure’s on and someone calls your bluff. There’s a persistence that can pay off:
RIKER: Gentlemen, we’re giving you an assignment. The one thing we don’t want to hear is that it’s impossible.
PICARD: I need the transporters to function, despite the hyperonic radiation.
LA FORGE: Yeah, but that’s imp… Yes, sir.

and then later:
LA FORGE: Captain – we can do it. We can modify the transporters. It’ll take fifteen years and a research team of a hundred…

Most impossible things are actually just really really difficult. For example, recently I asked a question on stackexchange about avoiding downtime during a migration. The first feedback I got was the comment “I don’t think [the downtime] can be avoided” which got a couple up-votes. But it turns out there was a way; it just took a lot of effort (I blogged about that effort in this series). It’s the yellow shirt’s job to explain the “how” and “what” so that the red shirts can make informed choices.

Later on in another episode:
LA FORGE: Ferengi codes are damn near impossible to break.
PICARD: Gentlemen, I have the utmost confidence in your ability to perform… the impossible.

See! It paid off. But it’s easy for me to give advice that boils down to “be able to do anything” it’s another thing to make it so. How do you get great at what you do?

… By Being Awesome

Easier said than done right?

So Geordi’s got skills, and he uses those skills to build a great career for himself. But where did he get those skills? I figure you can become great at something in a number of ways:

Be talented at it (you can’t change you, but you can change fields)
Hard work. There’s no getting by without hard work. Imagine you’re on vacation sitting on a beach. You’re looking at the hut next to you and see some guy in a chair reading SQL Server Pro on his iPad. You just can’t compete with that guy. I’m not suggesting you read trade magazines on holiday, but are you in a field where you might want to?

It boils down to finding your passion. And I hope you’ve found yours. Geordi is a guy whose passionate about the latest thing in his field. It almost seems like he cares about it more than job security:

PICARD: Warp without warp drive.
RIKER: They’re gonna put you out of a job, Geordi.
LA FORGE: I hope so, Commander.

Antilessons From Geordi

But I like my role models the same way I like a buffet. I pick and choose the good lessons and leave the rest behind. Here’s a Geordi “lesson” that didn’t make the cut:

LA FORGE: I don’t know, Data, my gut tells me we ought to be listening to what this guy’s trying to tell us.
DATA: Your gut?
LA FORGE: It’s just a… a feeling, you know, an instinct. Intuition.
DATA: But those qualities would interfere with rational judgment, would they not?
LA FORGE: You’re right, sometimes they do.
DATA: Then… why not rely strictly on the facts?
LA FORGE: Because you just can’t rely on the plain and simple facts. Sometimes they lie.

I don’t buy it. I get what he’s trying to say and the sentiment is correct, but the point of view is wrong. Facts don’t lie by definition. When Geordi says that facts lie sometimes, I would say instead that the facts are incomplete, or an assumption has been made incorrectly.

Once I was asked by a developer why his code was throwing the error message:

Table ‘noidentity’ does not have the identity property. Cannot perform SET operation.

even though the table in question was showing that it did have an identity column.

I said show me. And he did. And was it the same table? It was. And was it the same database? Yes. Are you sure? Of course. Can you show me the connection string? Hang on a second…. ohhhh. (Omitted in this mini-dialogue is about 15 minutes of head-scratching on both our parts).

Yes, sometimes the facts lie… but only when they’re not facts.

More Geordi Lessons in The Comments

I’ve put a few more of my favorites in the comment section of this post. Do you have any favorite Geordi lessons (or Star Trek lessons in general)?

Modifying Tables Online – Part 5: Just One More Thing

Michael J. Swart — Fri, 27 Apr 2012 14:50:29 +0000

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.

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

Just One More Thing

So Rob Volk commented on Part 3 of my blog. The comment prompted me to write a part five (which you’re reading now). So you can thank him for this bonus section.

I’m going to quote Rob directly. He wrote:

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)

He’s absolutely right. The switch step becomes much much simpler and simpler is almost always better (as it is in this case). My migration would start by creating some extra schemas and then creating the staging table. In the Adventureworks example I’ve been using, that looks something like this:

use AdventureWorks2012
go
create schema staging;
go
create schema obsolete;
go
CREATE TABLE staging.SalesOrderHeader(
-- etc...

So now our DB is prepared and looks like this:

Copy the data over to the staging table the same way. In my SalesOrderHeader example, that code doesn’t change at all except that I replace “Sales.SalesOrderHeader_new” with “Staging.SalesOrderHeader”.

When ready, the switch now includes code like this:

ALTER SCHEMA obsolete TRANSFER Sales.SalesOrderHeader;
ALTER SCHEMA Sales TRANSFER staging.SalesOrderHeader;
 
drop trigger obsolete.t_i_SalesOrderHeader;
drop trigger obsolete.t_u_SalesOrderHeader;
drop trigger obsolete.t_d_SalesOrderHeader;

Looks simple right! It is. Simple is better. The schema now looks something like this:

A few things to be careful of:

Foreign keys: Although no renaming is necessary, they still have to dropped from and to the obsolete table. And foreign keys pointing to the new table still need to be added.
Existing triggers: They should recreated on the new table.

That’s It

So dear reader, that’s the series. It was fun for me to explore in depth an intermediate topic. Thanks for bearing with me. I know the series was a little dry, but I figured that I wanted to use this walk-through for myself as a template for future migration projects and that if I thought it was useful, maybe you would think so too.

Modifying Tables Online – Part 4: Testing

Michael J. Swart — Thu, 26 Apr 2012 16:00:25 +0000

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

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

So my testing is also pretty straightforward. I

Backup the Adventureworks2012 database on my dev machine
Start sending a whack of queries to the database to simulate concurrent activity
Make Adventureworks2012.Sales.SalesOrderHeader larger by adding lots of rows
Migrate the database using the script from part 3
Look for any errors, fix them, restore the db and repeat the process if necessary
Otherwise … profit?

You should be able to handle each of these steps. Except that I want to explain exactly how I personally handle number 2: I use an application to send queries to the database many times. Sounds easy right? The trick is that I want to call the stored procedure many times at once.

Activity Generator

This is a C# program which I compile into a .net application (I can’t wait for .net 4.5′s async and await)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
 
namespace ConsoleApplication1 {
    class Program {
        static int counter = 0;
        static void Main( string[] args ) {
            SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
            cs.DataSource = @".";
            cs.InitialCatalog = "Adventureworks2012";
            cs.IntegratedSecurity = true;
            cs.AsynchronousProcessing = true;
            string connectionString = cs.ToString();
            string sql = @"s_DoSomething";
 
            for( int i = 0; i < 100000; i++ ) {
                SqlConnection conn = new SqlConnection( connectionString );
                conn.Open();
                SqlCommand cmd = new SqlCommand( sql, conn );
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.BeginExecuteNonQuery( new AsyncCallback( EndExecution ), cmd );
            }
            Console.WriteLine( string.Format( @"Error count: {0}", counter ) );
            Console.ReadLine();
        }
 
        static void EndExecution( IAsyncResult c ) {
            SqlCommand endCmd = ( c.AsyncState as SqlCommand );
            try {
                endCmd.EndExecuteNonQuery( c );
            } catch( Exception ex ) {
                //counter++;
                Interlocked.Increment( ref counter );
                Console.WriteLine( ex.Message );
            } finally {
                endCmd.Connection.Close();
            }
        }
    }
}

You might have noticed that this program is extremely boring. It’s just calling one stored procedure: s_DoSomething over and over as fast as it can. The advantage is that I’m free to modify s_DoSomething (which is for me a lot easier than modifying this application).

So what does this procedure do? I’m glad you asked:

s_DoSomething

This procedure executes at random one task chosen from a set of typical OLTP tasks that use the table Sales.SalesOrderHeader. Here’s the one I used:

create procedure s_DoSomething 
as
begin
 
    declare @choice int = 1 + RAND() * 6
    declare @SalesOrderId int;
 
    if (@choice = 1)
        insert Sales.SalesOrderHeader(DueDate, OrderDate, CustomerID, BillToAddressID, ShipToAddressID, ShipMethodID)
        select top 1000 DueDate, DATEADD(day, -1, DueDate), CustomerID, BillToAddressID, ShipToAddressID, ShipMethodID
        from Sales.SalesOrderHeader
    else if (@choice = 2)
        delete top (100) Sales.SalesOrderHeader
    else if (@choice = 3)
        begin
            declare @personid int;
 
            select top 1 @personid = BusinessEntityID
            from Sales.SalesPerson
            order by newid();
 
            select * 
            from sales.SalesOrderHeader
            where SalesPersonID = @personid;
        end;
    else if (@choice IN (4,5,6))
        begin
            select top 1 @SalesOrderId = SalesOrderId
            from Sales.SalesOrderHeader with (nolock);
 
            select top 100 * 
            from Sales.SalesOrderHeader 
            where SalesOrderId > @SalesOrderId
        end
 
end

Modifying Tables Online – Part 3: Example With Error Handling

Michael J. Swart — Fri, 20 Apr 2012 17:56:56 +0000

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

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
 
-- lowest SalesOrderId to migrate
select top 1 @i = so.SalesOrderId
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

Modifying Tables Online – Part 2: Implementation Example

Michael J. Swart — Tue, 17 Apr 2012 16:00:31 +0000

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

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;

Modifying Tables Online – Part 1: Migration Strategy

Michael J. Swart — Mon, 16 Apr 2012 16:00:08 +0000

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

Database Schema Changes

Database schema changes are inevitable. Some of these changes can be performed quickly (like altering ntext columns to nvarchar columns). Changes like these don’t have to process every single row in the table and we say that these quick changes are online.
Of course that means there are also such things as offline changes. These are changes that need to lock the table and process each row (like adding a persisted computed column to a table). Offline changes are sometimes okay when:

You have scheduled maintenance window to do work
The table isn’t that large or
the estimated amount of downtime is tolerable

But sometimes the estimated downtime is not tolerable and you want your changes to be online. Microsoft has improved and continue to improve SQL Server’s online features. For example:

Since 2005, we’ve had online index rebuilds (and even that has improved in SQL Server 2012)
Remus Rusanu talks about adding non-nullable columns as an online operation.

There are a few operations that are still not online. Here’s an example of one of them:

ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL

The downtime for this table change can last seconds or minutes depending on the size of the table. If your business can’t tolerate that downtime, what options are there? I’m going to explain one strategy which helps me with these following goals:

I want to add a rowversion column to a table. (But the strategy applies to many other offline changes).
The straightforward alter statement would lock the table for longer than we can tolerate
My change can place an exclusive lock on the table, but only for a short amount of time. This means that the change should work concurrently with quick transactions, but not necessarily with large bulk transactions.
It would be an advantage if this were a database-only solution because while I might understand the applications and processes that query my database, I don’t necessarily have as much control over when or how these queries are sent.

So here’s something that might work:

The Strategy

The strategy is fairly straightforward:

A) I create a staging table which will store the same data as the original table but with the new rowversion column. The staging table is empty at first:

B) Then I add triggers to the original table to keep the staging table up to date. Any changes in the original table will also be applied to matching data in the staging table.

C) Fill the staging table a little at a time. This processing may take time, but the original table is still available to others.

D) Finally remove triggers and rename the tables:

Some Notes

Dependencies I didn’t mention it yet, but we also have to worry about table dependencies. Tables can have a lot of dependencies (triggers, constraints, indexes etc…). You can create dependencies for the staging table, and rename them as well during the switch.

Step D, the Switch: The last step is the tricky one. It’s the one that takes a schema modification (Sch-M) lock on the tables. A Sch-M lock on the table is incompatible with every other kind of lock. So that means that no in-flight transactions are allowed to use the table during the switch. SQL Server handles it pretty well, but if there are long running queries on this table, there will be blocking.

What’s next?

In part 2, I implement this strategy for Adventureworks’ Sales.SalesOrderHeader table which seems to demonstrate every kind of table dependency SQL Server allows.
In part 3, I throw in error handling code to make the script more robust. So that if things go wrong, the application doesn’t notice and I don’t lose data.
In part 4, I explain the testing I do. It gives me more confidence that I don’t miss an update or a delete somehow.

Thanks

I posted a question on dba.stackexchange.com to look for some strategies. The answer I got there (thanks Brent!) is slightly different than the solution I’m describing here. Brent described a solution which used a view that UNION’s the staging table and the original table. It uses less space than the solution I describe, but it’s got some extra gotchas.

Some of this work was done in the course of my regular job. So thanks to Desire2Learn for letting me share some of the lessons I learned there. By the way, we’re hiring.

Portrait Contest (results)

Michael J. Swart — Wed, 11 Apr 2012 03:08:58 +0000

A couple weeks ago, I challenged you to identify two people based on my sketches of them. Luckily, I didn’t do so bad and the illustrations were easy to identify. (Unlike last week’s rush job on Tom Cruise and Cuba Gooding Jr.)

But 60 percent of you were able to correctly identify both portraits. The keen person might have noticed that the images were named buck.png and scott png. Here are those pictures.

Buck Woody

Buck Woody (Blog | @buckwoody) works at Microsoft and to me he’s Mr. Cloud, Mr. Azure. Everyone who entered knew who this was.

Scott Stauffer

Scott Stauffer (@SQLSocialite) For as long as I can remember, PASS + Canada = Scott. He’s a SSIS specialist and is super keen on community. He’s a super cool guy and was way too polite to point out that I drew his ears way way too big (I swear they grow bigger every time I look at this picture).

The Winner

So the winner is Jamie Thomson. Congratulations Jamie! He correctly identified both people from their photos. Jamie is another SSIS pro. I was actually lucky enough to meet him earlier this year.

I don’t know if you’ve noticed, but his blog has been on fire lately. Just lately he’s helped bring AdventureWorks to Azure. So thanks for everything Jamie!

Sending Query Results to Others

Michael J. Swart — Wed, 28 Mar 2012 16:00:17 +0000

I think most of you have all been here before. You want to know the results of a query, but you have to get it from a colleague through email or some other proxy:

So how do you usually do it? How do you ask someone to send you the results of a query? There’s a million ways to answer that question and I’ve discovered that none of them are perfect. Depending on the situation, different ways are better than others. So let’s pick a situation and explore the best way to ask for those query results.

The Situation

The facts were these…

Goal: I want the results of the following query. It retrieves the top 20 cached queries and plans based on total consumed cpu.
I have to rely on my friend to run the query for me.
My friend is not a database administrator but is comfortable around SQL Server Management Studio
I want to minimize or simplify instructions.
I don’t want any truncated results.
I want to assure my friend that these queries are safe.
For this situation, I’m not worried how my friend gets the data to me (email, ftp, etc…). I only care that my friend is able to persist the data to some file.

And to complete the situation, here’s that query I’m curious about…

-- TOP 20 queries (by CPU)
SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC

Notice that the results of this query contains xml, and that xml might contain commas, spaces, tabs, newlines and all the typical delimiters. That makes my job trickier. So now I’m going to list a number of methods and their pros and cons. I can think of a number of methods off the top of my head.

Method 1: Cut and paste into Excel
Method 2: Store results as tables in a temporary DB and back it up
Method 3: Using the export data wizard
Method 4: Use Management Studio’s results-to-file
Method 5: Use BCP along with SQLCMD mode
Method 6: Output the whole thing using FOR XML PATH (Spoiler alert, I like this one best!)

Method 1: Cut and Paste into Excel

This is most people’s usual method and it works in 90% of all cases because Excel is so versatile. However in this particular case, the whitespace in the query plans and the sql text mess up some of the rows and formatting, one field in each row. I get something like this, ugh:

But there’s a better way using Excel. It’s not too hard to use the Data Connection Wizard. So it’s not too much work to guide my friend through it either. If I can, then this is a really useful nice way to pass around data. That wizard is accessed like this:

Method 2: Store Results As Tables in a Temporary DB and Back It Up

That can be done with a single T-SQL script which my friend should be able to run no problem. The script looks something like this:

create database myresults
go
-- TOP 20 queries (by CPU)
SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
INTO myresults.dbo.myresultset
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC
go
 
backup database myresults to disk = 'C:\Users\Michael Swart\Desktop\Output.bak'
go
 
drop database myresults
go

The best part of this is that the data is backed up perfectly, the data captures all the data with no truncation. Xml fields are stored as xml fields and once restored, it’s easy to access the resultset exactly as it was on the target computer.

The bad news is that although it should be fairly safe, it’s not as easy to assure my friend that the query above changes nothing. It does! It creates a whole database and backs it up. It then drops that database.

Hang on a second, if my friend can use this method, that means my friend (the non-dba) is authorized to drop databases. Yikes! What’s up with that?

Method 3: Using the Export Data Wizard

It seems like SQL Server’s “Export Data” wizard was just built for this job. That can be accessed here:

But I don’t recommend it, not one bit. It launches a wizard which takes me through a set of choices, and no matter what I choose, it always seems to choke on the xml column.

It’s clear that this Export Data Wizard is using SQL Server Integration Services (SSIS) under the covers. How do I know it’s SSIS under the covers? Well if the clumsy data type handling weren’t a giveaway (zing!), the wizard can save the job I specified as an SSIS package.

The SSIS shows through too much to recommend to my friend, who has no dreams of becoming a B.I. Maestro.

So it seems like this method is only appropriate for those situations when cutting and pasting to Excel would be just as appropriate. And it’s kind of hard to write instructions simpler than “Cut and paste into Excel”.

Method 4: Use Management Studio’s Results-To-File

Most people use the query window’s default setting, Results-To-Grid. Sometimes there’s a use for Results-To-Text (like when using sp_helptext for example). But what about Results-To-File? I can set that using the Query window’s context menu here:

I can also enable results to file with Ctrl+Shift+F. I had to look that up though. There’s a reason I haven’t learned that shortcut by heart. It’s because results-to-file doesn’t work very well. As far as I can tell, it’s like specifying results-to-text and then pasting that into a text file.

The biggest problem here is that depending on the results, the data gets truncated easily and the newlines and tabs in the data get mixed up with the rest of the formatting. By the way, I’m also out of luck (and for the same reason) if I use SSMS to save grid results.

Method 5: Use BCP, along with SQLCMD mode

If I can instruct my friend to use a query window in SQLCMD mode, then I can write a script which will get a bcp script to export data. Enable SQLCMD mode by choosing it from the Query menu in SSMS:

And the script I would have my friend run looks like this:

!!bcp "Select 'query omitted' as [BigLongQuery]" queryout "c:\temp\desktop\output5.dat" -SSWARTLAPTOP -T -n

which I could then get back on my side using bcp or using OPENROWSET.

This method is okay. It’s kind of awkward (and any instructions to my friend will be equally as awkward) and it always takes some work to get the syntax just right. But BCP is kind of like Rob Lowe, he hasn’t changed in years and he’s really not that bad.

Method 6: Output the Whole Thing Using FOR XML PATH

Hey! Now I’m getting somewhere! XML was built for this kind of thing. All I do is take the query, and append FOR XML PATH to it and send the query to my friend saying “Run this and paste the results into notepad.” Here’s the whole query:

SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC
FOR XML PATH

And the results that are sent back are immediately accessible without any processing.

I think this method works best for this particular situation. It hits my goals perfectly. Now if you dear reader want to use this method in your situation, there’s just some extra caveats:

SSMS restricts XML data results to 2 megabytes, although that’s configurable.
If you query binary fields (say query plan handles for example), then you might be surprised if the results come back in base64 encoding rather than hex which is a bit of a pain.

Other methods

Powershell I know you powershell pros are eager to give a one line script that does everything here. So lets hear it the comments! I know that powershell treats sets of objects (rather than unix’s cshell which deals with text). So I’m curious how well it serializes objects to a file.

SQL Server Integration Services I haven’t forgotten about SSIS either, it’s the tool that was built for moving data from here to there in some format (any here, any there, any format). But I just couldn’t get the instructions simple enough for this scenario.

I’ll Draw Your Portrait

Michael J. Swart — Mon, 19 Mar 2012 22:19:52 +0000

Update April 10, 2012: Results are here!

Hey I’m back after taking a “me” week. And you can look forward to some real technical SQL stuff very soon. But first a contest!

A lot of people have mentioned that they like my cartoon illustrations. And those are great comments. I have a lot of fun drawing them! For example here are a couple of the illustrations that I’ve done for some friends:

Friend 1:

Friend 2:

I’ll Draw You Too

So I’ll draw you for first prize in a contest! Take a look at the faces of my friends above, guess their names and fill in the form below. I’m accepting submissions until Friday, March 23 at 12:00 noon, Eastern time. I’ll select one random winner from correct responses (spelling counts!) and if you’re that winner, I’ll contact you to do a picture for you (or even a loved one).

Thanks to everyone who submitted! I’ll announce the winner shortly

Good luck!