T-SQL Bitwise Operations

I’ve seen bit-product columns from time-to-time, mostly in SQL Server 2000 system tables, but it’s never been something I’ve had to work with. And when I’ve needed to, I’ve known how to figure out which options are selected, i.e. a bit product of 9 means options 8 and 1 are selected. If you’ve ever taken a look at the [status] column on the sysdatabases table (SQL 2000), you’ll know what I’m talking about.

What I’ve never known how to do, until recently, was calculate these options programmatically. That’s why, when I noticed the [freq_interval] on the sysschedules table was a bit-product column, I decided to spend a little time figuring it out. Fortunately for me, a couple of my awesome co-workers, Jeff M. and Jason H., have worked with this before and were able to explain it to me. And, it turns out, it’s actually quite easy.

Let me back up a few steps in case you’re not familiar with this topic. If you check out the Books Online entry for the sysschedules table (2005), you’ll notice the following statement:

freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

When I looked at the actual value in the table, the schedule has a [freq_interval] value of 42, which is the sum of the bit values for the days selected.

If there were more than 7 options, the bit values would continue to double, i.e. 128, 256, etc. And regardless of how many bit values you select, you’re guaranteed one and only one possible answer, as the sum of all previous bit values will never exceed the next bit value:
1 + 2 = 3
1 + 2 + 4 = 7
1 + 2 + 4 + 8 = 15

Knowing this, I’m able to retrieve the values manually: I start with the highest bit value that does not exceed 42, then subtract it; I repeat until I’m left with 0.

So…
42 – 32 = 10
10 – 8 = 2
2 – 2 = 0

That means my job is scheduled to run on Friday’s (32), Wednesday’s (8), and Monday’s (2).

Now how do I do this with T-SQL? SQL Server provides an operator specifically for this task: the bitwise AND operator (&). For now, I’m going to skip the “why” behind this and just get to the practical application. If you’re interested in the “why,” let me know and I’ll write a follow-up post on binary and logical AND and OR operations.

For example, to use the bitwise AND to find out which days are selected…

Select 42 & 1 As 'Sunday'
    , 42 & 2 As 'Monday'
    , 42 & 4 As 'Tuesday'
    , 42 & 8 As 'Wednesday'
    , 42 & 16 As 'Thursday'
    , 42 & 32 As 'Friday'
    , 42 & 64 As 'Saturday';

… will return …

Sunday      Monday      Tuesday     Wednesday   Thursday    Friday      Saturday
----------- ----------- ----------- ----------- ----------- ----------- -----------
0           2           0           8           0           32          0

If the result is not equal to zero, then that day is selected. Easy as key lime pie, right?

Now let’s take it a step further and create our own working example. Let’s say we’re going to track the characteristics of various objects in a single bit-product column (note: this is not necessarily the best way to accomplish this in the real world, but it’s a good illustration). First, set up a table to use in our example. This table will have a column, [attributes], which will hold the sum of our bit values.

Create Table myTable
(
      id            int identity(1,1)
    , item          varchar(10)
    , attributes    int
);
 
Insert Into myTable
Select 'Broccoli', 200 Union All
Select 'Tomato', 193 Union All
Select 'Car', 276 Union All
Select 'Ball', 292;

Next, we’re going to create a table variable that holds characteristics and their values. We’ll then join these two tables together to see which attributes exist for each item.

Declare @statusLookup Table
(
      attribute int
    , value     varchar(10)
);
 
Insert Into @statusLookup
Select 1, 'Red' Union All
Select 4, 'Blue' Union All
Select 8, 'Green' Union All
Select 16, 'Metal' Union All
 
Select 32, 'Plastic' Union All
Select 64, 'Plant' Union All
Select 128, 'Edible' Union All
Select 256, 'Non-Edible';
 
Select a.item, b.value
From myTable a
Cross Join @statusLookup b
Where a.attributes & b.attribute <> 0
Order By a.item
    , b.value

You should get this result:

item       value
---------- ----------
Ball       Blue
Ball       Non-Edible
Ball       Plastic
Broccoli   Edible
Broccoli   Green
Broccoli   Plant
Car        Blue
Car        Metal
Car        Non-Edible
Tomato     Edible
Tomato     Plant
Tomato     Red

Great, now we know broccoli is edible! Let’s apply a little XML to clean up the results…

Select a.item
    , Replace( Replace( Replace(( 
        Select value 
        From @statusLookup AS b 
        Where a.attributes & b.attribute <> 0 
        Order By b.value For XML Raw)
        , '"/><row value="', ', '), '<row value="', ''), '"/>', '') 
        As 'attributes'
From myTable a
Order By a.item;
item       attributes
----------------------------------------
Ball       Blue, Non-Edible, Plastic
Broccoli   Edible, Green, Plant
Car        Blue, Metal, Non-Edible
Tomato     Edible, Plant, Red

Voila! There you have it, how to use the bitwise AND (&) operator to retrieve multiple values from a bit-product column. Pretty neat stuff!

Special thanks to Jeff M. and Jason H. for their assistance. :)

Happy Coding!

Michelle Ufford (aka SQLFool)

Source: http://sqlfool.com/2009/02/bitwise-operations/

Fragmentation on Replicated Tables

I’ve heard more than one person state that they do not need to defrag their tables, because they’re replicated and the source tables are defragged regularly. Let’s set this myth straight.

Repeat after me: defragmentation processes are not replicated.

Good. Thank you.

For those non-believers, let me prove it:

SET NOCOUNT ON;
 
/* Create publication db */
CREATE DATABASE sandbox_publisher;
GO
 
/* Create subscription db */
CREATE DATABASE sandbox_subscriber;
GO
 
/* Navigate to publisher db */
USE sandbox_publisher;
GO
 
/* Create a table to play with */
CREATE TABLE myTable
(
      myID UNIQUEIDENTIFIER
    , myColumn VARCHAR(2000)
 
    CONSTRAINT PK_myTable PRIMARY KEY CLUSTERED (myID)
);
/* NOTE: please never, ever use this
   type of clustered index in real-life.
   The whole purpose is to generate
   substantial fragmentation for 
   illustrative purposes. */
 
/* Populate it with some non-sequential
   records to generate fragmentation */
DECLARE @iteration INT;
SET @iteration = 0;
 
WHILE @iteration < 1000
BEGIN
    INSERT INTO myTable
    SELECT NewID(), REPLICATE('A', 2000);
 
    SET @iteration = @iteration + 1;
END
 
/* Now let's verify that we really did
   make a mess of our clustered index */
SELECT avg_fragmentation_in_percent
    , page_count
FROM sys.dm_db_index_physical_stats 
(DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') 
OPTION (MaxDop 1);
 
/* Results:
 
avg_fragmentation_in_percent page_count
---------------------------- --------------------
99.0157480314961             508
 
99% fragmented, not too shabby!  Your results 
may vary slightly, based on the guid values
generated by NewID().
*/
 
/* Now, let's set up replication */
USE sandbox_publisher;
GO
 
/* Enable publication */
EXECUTE SP_REPLICATIONDBOPTION
      @dbname = N'sandbox_publisher'
    , @optname = N'publish'
    , @VALUE = N'true';
GO
 
/* Create our publication */
EXECUTE SP_ADDPUBLICATION
      @publication = N'myTestPublication' 
    , @sync_method = N'concurrent'
    , @description = N'Test Publication'
    , @status = N'active'
    , @independent_agent = N'true'
    , @immediate_sync = N'true'
    , @retention = 0
GO
 
/* Create our snapshot agent */
EXECUTE SP_ADDPUBLICATION_SNAPSHOT
      @publication = N'myTestPublication'
    , @frequency_type = 1;
 
/* Add our table as an article */
EXECUTE SP_ADDARTICLE
      @publication = N'myTestPublication'
    , @article = N'myTable'
    , @source_owner = N'dbo'
    , @source_object = N'myTable'
    , @type = N'logbased'
    , @destination_table = N'myTable'
    , @destination_owner = N'dbo'
    , @ins_cmd = N'CALL sp_MSins_dbomyTable'
    , @del_cmd = N'CALL sp_MSdel_dbomyTable'
    , @upd_cmd = N'MCALL sp_MSupd_dbomyTable';
GO
 
/* Generate an initial snapshot */
EXECUTE sp_startpublication_snapshot
      @publication = N'myTestPublication';
 
/* Create our subscription */
EXECUTE SP_ADDSUBSCRIPTION 
      @publication = N'myTestPublication'
    , @subscriber = N'YourServerName'
    , @destination_db = N'sandbox_subscriber';
 
/* At this point, basic transactional replication 
   should be configured.  You should now have a 
   copy of myTable in your subscription database. */
 
USE sandbox_subscriber;
GO
 
/* Let's take a look at fragmentation on the 
   subscription database. */
SELECT avg_fragmentation_in_percent
    , page_count
FROM sys.dm_db_index_physical_stats 
(DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') 
OPTION (MaxDop 1);
 
/* Results:
 
avg_fragmentation_in_percent page_count
---------------------------- --------------------
0.29940119760479             334
 
Less than 1% fragmented!  Why is this?  During
initialization, a copy of the schema and data
is sent to the subscriber.  The data is sorted
before insertion into the destination table.
*/
 
USE sandbox_publisher;
GO
 
/* Let's insert another 1000 records and see
   what happens... */
DECLARE @iteration INT;
SET @iteration = 0;
 
WHILE @iteration < 1000
BEGIN
    INSERT INTO sandbox_publisher.dbo.myTable
    SELECT NewID(), REPLICATE('A', 2000);
 
    SET @iteration = @iteration + 1;
END
 
SELECT avg_fragmentation_in_percent
    , page_count
FROM sys.dm_db_index_physical_stats 
(DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') 
OPTION (MaxDop 1);
 
/* Results:
 
avg_fragmentation_in_percent page_count
---------------------------- --------------------
98.6193293885602             1014
 
The publisher is still very fragmented 
(as expected) */
 
USE sandbox_subscriber;
GO
 
SELECT avg_fragmentation_in_percent
    , page_count
FROM sys.dm_db_index_physical_stats 
(DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') 
OPTION (MaxDop 1);
 
/* Results:
 
avg_fragmentation_in_percent page_count
---------------------------- --------------------
95.4034729315628             979
 
But now the subscriber is fragmented too!  This
is because each individual transaction is 
replicated, so out-of-order inserts affect
both the publishing and the subcribing 
databases equally.
*/
 
/* Finally, let's rebuild our index on the 
   publisher and see what happens */
USE sandbox_publisher;
GO
 
ALTER INDEX PK_myTable ON myTable Rebuild;
 
SELECT avg_fragmentation_in_percent
    , page_count
FROM sys.dm_db_index_physical_stats 
(DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') 
OPTION (MaxDop 1);
 
/* Results:
 
avg_fragmentation_in_percent page_count
---------------------------- --------------------
0                            667
 
0% fragmentation in the publishing table!  
What about our subscribing table?
*/
 
USE sandbox_subscriber;
GO
 
SELECT avg_fragmentation_in_percent
    , page_count
FROM sys.dm_db_index_physical_stats 
(DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') 
OPTION (MaxDop 1);
 
/* Results:
 
avg_fragmentation_in_percent page_count
---------------------------- --------------------
95.4034729315628             979
 
As you can see, the subcribing table was
completely unaffected by the defrag
activities on the publisher.
*/

So there you have it, proof that defrag operations are not replicated! Now go defrag your replicated tables. :)

Oh, and if you need help defragging your indexes, check out my index defrag script (2005, 2008).

Creating a 60 GB Index

Recently, I needed to create an index on a 1.5 billion row table. I’ve created some large indexes before, but this was the largest, so I thought I’d share my experience in case anyone was interested.

The plan was to create the following index:

Create NonClustered Index IX_indexName_unpartitioned
    On databaseName.dbo.tableName(columnList)
    Include (includedColumnList)
    With (MaxDop = 1, Online = On, Sort_In_TempDB = On)
    On [Primary];

This is an unpartitioned, non-clustered index being built on a partitioned table. Normally, when you build an aligned partitioned index, the index creation process requires less memory and has less noticeable impact on the system, because each partition is handled individually, one at a time. But as this is an unpartitioned (unaligned) index, each partition was built concurrently, requiring more memory and causing a greater impact on performance. Because of this, I needed to restrict the process to MaxDop 1; otherwise, the server would suffer because of too much memory pressure.

I chose Sort_In_TempDB = On because:

  • I’m building this index online on a very busy table and cannot afford to impact normal oeprations. By using Sort_In_TempDB = On, index transactions are separated from user transactions, allowing the user transaction log to be truncated. *
  • TempDB is on a different volume and therefore should reduce the duration of the operation.
  • The recovery for the user database is full, and the recovery for the TempDB is simple. Sorting in TempDB would minimize logging.

* Note: the transaction log for the user database still grew at a much faster rate than normal and had to be closely monitored during this operation to ensure enough free space remained.

The size of the indexed columns is 25 bytes. So I ran my calculations and came up with 36gb space requirement. We increased TempDB to 50gb and gave it a go. An hour later… ERROR. The process terminated because there would not be enough space free in TempDB to complete the operation. Obviously, my calculations were incorrect. After speaking with Chris Leonard, a man who is way too smart for his own good, I realized I had not included my clustered index in the size calculations. Doh.

Re-running my estimates, here’s what I came up with:

Index Size 25   bytes
Clustered Index Size 16   bytes
Records per Page 197
Est. Rows 1,575,000,000
Est. Number of Pages 7,995,000
Space Requirements 59   GB

 

Obviously, 50gb of free space just wasn’t going to cut it. I decided to give TempDB a little wiggle room and bumped up the space in TempDB to 70gb (not as easy as you’d imagine, I had to requisition more space on the SAN), then re-ran the operation. Success! The process completed in 3 hours and 24 minutes. There was a mild increase in CPU, but no applications or users experienced any issues.

For those interested in the particulars: this was used for a single-record look-up and could not be filtered by the partitioning key. The non-partitioned version of this index has 80% less reads and 11% less CPU than its partitioned counterpart.

If you’re interested in learning more about indexes, here’s some recommended reading:

Indexing for Partitioned Tables

So you’ve partitioned your table, and now you’re ready to performance tune. As with any table, indexing is a great place to start. And if you’re like most people new to partitioning, you probably created all of your indexes on the partitioned scheme, either by design or unintentionally.

Let’s take a step back here and discuss what a partitioned index is. A partitioned index, like a partitioned table, separates data into different physical structures (partitions) under one logical name. Specifically, each partition in a nonclustered index contains its own B-tree structure with a subset of rows, based upon the partitioning scheme. By default, an unpartitioned nonclustered index has just one partition.

Keep in mind, when you create an index on a partitioned table, i.e.

Create NonClustered Index IX_myIndex
    On dbo.myTable(myColumn);

… you are creating the index on the partitioned scheme by default. In order to create a NON-partitioned index on that same table, you would need to explicitly declare “On [FileGroup]“, i.e.

Create NonClustered Index IX_myIndex
    On dbo.myTable(myColumn)
    On [Primary];

 

But should you partition your index? That depends on how you use it. In fact, most environments will probably want to use a mix of partitioned and non-partitioned indexes. I’ve found that that partitioned indexes perform better when aggregating data or scanning partitions. Conversely, you’ll probably find that, if you need to locate a single, specific record, nothing performs better than a non-partitioned index on that column.

Let’s walk through some examples and see how they perform. I’ll bring back my trusty ol’ orders table for this.

/* Create a partition function. */
Create Partition Function 
    [test_monthlyDateRange_pf] (datetime)
    As Range Right For Values
    ('2009-01-01', '2009-01-08', '2009-01-15'
    , '2009-01-22', '2009-01-29');
Go
 
/* Associate the partition function with a partition scheme. */
Create Partition Scheme test_monthlyDateRange_ps
    As Partition test_monthlyDateRange_pf
    All To ([Primary]);
Go
 
/* Create a partitioned table. */
Create Table dbo.orders
(
      order_id  int Identity(1,1)   Not Null
    , orderDate datetime            Not Null
    , orderData smalldatetime       Not Null
 
    Constraint PK_orders Primary Key Clustered
    (
        order_id
      , orderDate
    )
) On test_monthlyDateRange_ps(orderDate);
Go
 
/* Create some records to play with. */
Set NoCount On;
 
Declare @endDate datetime = '2009-01-01';
 
While @endDate < '2009-02-01'
Begin
 
    Insert Into dbo.orders
    Select @endDate, @endDate;
 
    Set @endDate = DATEADD(minute, 1, @endDate);
 
End;
 
Set NoCount Off;
 
 
/* Let’s create an aligned, partitioned index. */
Create NonClustered Index IX_orders_aligned
    On dbo.orders(order_id)
    On test_monthlyDateRange_ps(orderDate); 
    /* you don't actually need to declare the last
       line of this unless you want to create the
       index on a different partitioning scheme.   */
 
/* Now let’s create an unpartitioned index. */
Create NonClustered Index IX_orders_unpartitioned
    On dbo.orders(order_id)
    On [Primary];

 

Now that we have both a partitioned and an unpartitioned index, let’s take a look at our sys.partitions table:

/* Let's take a look at our index partitions */
Select i.name
    , i.index_id
    , p.partition_number
    , p.rows
From sys.partitions As p
Join sys.indexes As i
    On p.object_id = i.object_id 
   And p.index_id = i.index_id
Where p.object_id = object_id('orders')
Order By i.index_id, p.partition_number;

 

sys.partitions

sys.partitions

As expected, both of our partitioned indexes, PK_orders and IX_orders_aligned, have 6 partitions, with a subset of rows on each partition. Our unpartitioned non-clustered index, IX_orders_unpartitioned, on the other hand has just 1 partition containing all of the rows.

Now that we have our environment set up, let’s run through some different queries and see the performance impact of each type of index.

/* Query 1, specific record look-up, covered */
Select order_id, orderDate
From dbo.orders With (Index(IX_orders_aligned))
Where order_id = 25000;
 
Select order_id, orderDate
From dbo.orders With (Index(IX_orders_unpartitioned))
Where order_id = 25000;

 

Query 1

Query 1

The unpartitioned index performs significantly better when given a specific record to look-up. Now let’s try the same query, but utilizing a scan instead of a seek:

/* Query 2, specific record look-up, uncovered */
Select order_id, orderDate, orderData
From dbo.orders With (Index(IX_orders_aligned))
Where order_id = 30000;
 
Select order_id, orderDate, orderData
From dbo.orders With (Index(IX_orders_unpartitioned))
Where order_id = 30000;

 

Query 2

Query 2

Again we see that the non-partitioned index performs better with the single-record look-up. This can lead to some pretty dramatic performance implications. So when *would* we want to use a partitioned index? Two instances immediately pop to mind. First, partition switching can only be performed when all indexes on a table are aligned. Secondly, partitioned indexes perform better when manipulating large data sets. To see this in action, let’s try some simple aggregation…

/* Query 3, aggregation */
Select Cast(Round(Cast(orderdate As float), 0, 1) As smalldatetime) As 'order_date'
    , Count(*)
From dbo.orders With (Index(IX_orders_aligned))
Where orderDate Between '2009-01-01' And '2009-01-07 23:59'
Group By Cast(Round(Cast(orderdate As float), 0, 1) As smalldatetime) 
Order By Cast(Round(Cast(orderdate As float), 0, 1) As smalldatetime);
 
Select Cast(Round(Cast(orderdate As float), 0, 1) As smalldatetime) As 'order_date'
    , Count(*)
From dbo.orders With (Index(IX_orders_unpartitioned))
Where orderDate Between '2009-01-01' And '2009-01-07 23:59'
Group By Cast(Round(Cast(orderdate As float), 0, 1) As smalldatetime) 
Order By Cast(Round(Cast(orderdate As float), 0, 1) As smalldatetime);

 

Query 3

Query 3

As you can see, partitioned indexes perform better when aggregating data. This is just a simple example, but the results can be even more dramatic in a large production environment. This is one of the reasons why partitioned tables and indexes are especially beneficial in data warehouses.

So now you have a general idea of what a partitioned index is and when to use a partitioned index vs a non-partitioned index. Ultimately, your indexing needs will depend largely on the application and how the data is used. When in doubt, test, test, test! So to recap…

  • Specify “On [FileGroup]“ to create an unpartitioned index on a partitioned table
  • Consider using non-partitioned indexes for single-record look-ups
  • Use partitioned indexes for multiple records and data aggregations
  • To enable partition switching, all indexes on the table must be aligned.

For more information on partitioning, check out my other partitioning articles:

Partitioning Example
Partitioning 101
Tips for Large Data Stores

Error Handling in T-SQL

Error handling is one of those things in SQL Server that just doesn’t get the attention it deserves. Even a properly constructed stored procedure can still result in error, such as primary key or unique constraint errors.

Why should you care? Consider this real-world example:

You’re a DBA monitoring a well-performing environment. You deploy a new application to production. Suddenly, performance degrades but you do not know why. You look in your error log and see a whole mess of primary key errors. Digging into your newly deployed application, you find that you are now making an extra (and unnecessary) insert to the database, which is resulting in error and causing your performance issues.

This is just one example of many. Fortunately, SQL 2005 has really simplified the error handling process with features such as the Try/Catch block.

The basic components of error handling are:

  • Try…Catch block (2005/2008)
  • Error identification
  • Transaction handling
  • Error logging (optional)
  • Error notification

As an early holiday gift, here’s a generic error handling process to get you started:

If ObjectProperty(Object_ID('dbo.dba_logError_sp'), N'IsProcedure') = 1
Begin
    Drop Procedure dbo.dba_logError_sp;
    Print 'Procedure dba_logError_sp dropped';
End;
Go
 
If ObjectProperty(Object_ID('dbo.dba_errorLog'), N'IsTable') Is Null
Begin
 
    Create Table dbo.dba_errorLog
    (         errorLog_id       int identity(1,1) 
            , errorType         char(3)     
                Constraint [DF_errorLog_errorType] Default 'sys' 
            , errorDate         datetime	
                Constraint [DF_errorLog_errorDate] Default(GetDate())
            , errorLine         int
            , errorMessage      nvarchar(4000)
            , errorNumber       int
            , errorProcedure    nvarchar(126)
            , procParameters    nvarchar(4000)
            , errorSeverity     int
            , errorState        int
            , databaseName      nvarchar(255)
        Constraint PK_errorLog_errorLogID Primary Key Clustered
        (
            errorLog_id	
        )
    );
 
    Print 'Table dba_errorLog created';
 
End;
Go
 
 
Set ANSI_Nulls On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set NoCount On;
Set Numeric_RoundAbort Off;
Set Quoted_Identifier On;
Go
 
Create Procedure dbo.dba_logError_sp
(
    /* Declare Parameters */
      @errorType            char(3)         = 'sys'
    , @app_errorProcedure   varchar(50)     = ''
    , @app_errorMessage     nvarchar(4000)  = ''
    , @procParameters       nvarchar(4000)  = ''
    , @userFriendly         bit             = 0
    , @forceExit            bit             = 1
    , @returnError          bit             = 1
)
As
/***************************************************************
    Name:       dba_logError_sp
 
    Author:     Michelle F. Ufford, http://sqlfool.com
 
    Purpose:    Retrieves error information and logs in the 
                        dba_errorLog table.
 
        @errorType = options are "app" or "sys"; "app" are custom 
                application errors, i.e. business logic errors;
                "sys" are system errors, i.e. PK errors
 
        @app_errorProcedure = stored procedure name, 
                needed for app errors
 
        @app_errorMessage = custom app error message
 
        @procParameters = optional; log the parameters that were passed
                to the proc that resulted in an error
 
        @userFriendly = displays a generic error message if = 1
 
        @forceExit = forces the proc to rollback and exit; 
                mostly useful for application errors.
 
        @returnError = returns the error to the calling app if = 1
 
    Called by:	Another stored procedure
 
    Date        Initials    Description
	----------------------------------------------------------------------------
    2008-12-16  MFU         Initial Release
****************************************************************
    Exec dbo.dba_logError_sp
        @errorType          = 'app'
      , @app_errorProcedure = 'someTableInsertProcName'
      , @app_errorMessage   = 'Some app-specific error message'
      , @userFriendly       = 1
      , @forceExit          = 1
      , @returnError        = 1;
****************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    /* Declare Variables */
    Declare	@errorNumber            int
            , @errorProcedure       varchar(50)
            , @dbName               sysname
            , @errorLine            int
            , @errorMessage         nvarchar(4000)
            , @errorSeverity        int
            , @errorState           int
            , @errorReturnMessage   nvarchar(4000)
            , @errorReturnSeverity  int
            , @currentDateTime      smalldatetime;
 
    Declare @errorReturnID Table (errorID varchar(10));
 
    /* Initialize Variables */
    Select @currentDateTime = GetDate();
 
    /* Capture our error details */
    If @errorType = 'sys' 
    Begin
 
        /* Get our system error details and hold it */
        Select 
              @errorNumber      = Error_Number()
            , @errorProcedure   = Error_Procedure()
            , @dbName           = DB_Name()
            , @errorLine        = Error_Line()
            , @errorMessage     = Error_Message()
            , @errorSeverity    = Error_Severity()
            , @errorState       = Error_State() ;
 
    End
    Else
    Begin
 
    	/* Get our custom app error details and hold it */
        Select 
              @errorNumber      = 0
            , @errorProcedure   = @app_errorProcedure
            , @dbName           = DB_Name()
            , @errorLine        = 0
            , @errorMessage     = @app_errorMessage
            , @errorSeverity    = 0
            , @errorState       = 0 ;
 
    End;
 
    /* And keep a copy for our logs */
    Insert Into dbo.dba_errorLog
    (
          errorType
        , errorDate
        , errorLine
        , errorMessage
        , errorNumber
        , errorProcedure
        , procParameters
        , errorSeverity
        , errorState
        , databaseName
	)
    OutPut Inserted.errorLog_id Into @errorReturnID
    Values
    (
          @errorType
        , @currentDateTime
        , @errorLine
        , @errorMessage
        , @errorNumber
        , @errorProcedure
        , @procParameters
        , @errorSeverity
        , @errorState
        , @dbName
    );
 
    /* Should we display a user friendly message to the application? */
    If @userFriendly = 1
        Select @errorReturnMessage = 'An error has occurred in the database (' + errorID + ')'
        From @errorReturnID;
    Else 
        Select @errorReturnMessage = @errorMessage;
 
    /* Do we want to force the application to exit? */
    If @forceExit = 1
        Select @errorReturnSeverity = 15
    Else
        Select @errorReturnSeverity = @errorSeverity;
 
    /* Should we return an error message to the calling proc? */
    If @returnError = 1
        Raiserror 
        (
              @errorReturnMessage
            , @errorReturnSeverity
            , 1
        ) With NoWait;
 
    Set NoCount Off;
    Return 0;
 
End
Go

 

You would then call this proc in the following manner:

Begin Try
 
    /* If a business logic error exists, then call this proc */
    If 1 != 1
        Execute dbo.dba_logError_sp 
              @errorType            = 'app'
            , @app_errorProcedure   = 'yourStoredProcedureName'
            , @app_errorMessage     = '1 does not equal 1!'
            , @forceExit            = 1;
 
    /* Start a new transaction */
    Begin Transaction;  
 
    /* Do something */
 
    /* If you have an open transaction, commit it */
    If @@TranCount > 0
        Commit Transaction;
 
End Try
Begin Catch
 
    /* Whoops, there was an error... rollback! */
    If @@TranCount > 0
        Rollback Transaction;
 
    /* Grab our proc parameters */
    Set @errorParameters = '@myVariable = ' + @myVariable;
 
    /* Return an error message and log it */
    Execute dbo.dba_logError_sp
        @procParameters = @errorParameters;
 
End Catch;

 

Some things to keep in mind:

  • Error handling is not a “one-size-fits-all” process. Make sure you’re handling the error appropriately for your environment.
  • Be careful when working with nested transactions; you can sometimes get unexpected results.
  • Only errors with a severity levels greater than 10 will be caught by the Catch block.
  • You can initiate an error within your stored procedure by using RaisError().

Happy coding holidays! :)

SQL Tweaks and Tools That Make My Life Easier

It still surprises me how many people don’t know about some of the very things that make my job so much easier. So this next post is dedicated to sharing some of the tweaks and tools I’ve run across that will help anyone who works with SQL:

 

Indexes
Anyone who uses included columns is probably well aware of the frustrations that can come from having to look up information on which columns are included. I wrote a stored procedure, dba_indexLookup_sp, to help me with this, before discovering sp_helpindex2. If you haven’t heard of sp_helpindex2, it’s a re-write of sp_helpindex by Kimberly Tripp. You can find it on Kimberly’s blog. The main difference is Kimberly’s is a system stored procedure (mine is not) and my version returns partitioning information (Kimberly’s does not). Check both out and use whichever one meets your needs best.

 

KeyBoard ShortCuts

In SQL Server Management Studio (SSMS), click on:
    Tools –> Options… –> Environment –> Keyboard

Keyboard Shortcuts

Keyboard Shortcuts

For your copying convenience:

Ctrl+3   Select Top 100 * From
Ctrl+4   sp_tables @table_owner = ‘dbo’
Ctrl+5   sp_columns
Ctrl+6   sp_stored_procedures @sp_owner = ‘dbo’
Ctrl+7   sp_spaceused
Ctrl+8   sp_helptext
Ctrl+9   dba_indexLookup_sp or sp_helpindex2

Please note that these settings will not take effect until you open a new query window. Here’s an example of how you could use this: use Ctrl+4 to find a list of tables, then copy one into your query window; to view a sample of that table’s data, highlight the table name (I usually double-click on it) and press Ctrl+3. It’s a thing of beauty. Oh, and you may want to remove/change the schema filters if you use schemas other than dbo.

 

Query Execution Settings

After having one too many issues arise from non-DBA’s connecting to the production environment to run a devastating ad hoc, I’ve had all of our developers and analysts adopt the following settings. The only thing difference between my setting and theirs is that I have “Set Statistics IO” selected. FYI – you can also make these same setting changes in Visual Studio.

In SQL Server Management Studio (SSMS), click on:
    Tools –> Options… –> Query Execution –> SQL Server –> Advanced

Query Execution Settings

Query Execution Settings

 

Copy Behavior
This next tip actually has nothing to do with SQL Server, and can be done with any Microsoft product. However, I just learned about it a few weeks ago and already I use it quite frequently.

Holding down “Alt” while you drag your mouse will change your selection behavior to block selection.

Block Selection

Block Selection

 

Please note: The following tools requires SQL 2008 Management Studio. These tools will also work when you connect SQL 2008 SSMS to a 2005 instance.

 

Object Detail Explorer

Finally, there’s a reason to use the Object Detail Explorer! My favorite use is to quickly find the table size and row counts of all the tables in a database. If these options are not currently available, you may just need to right click on the column headers and add it to the display.

Object Detail Explorer

Object Detail Explorer

 

Missing Indexes

And lastly, when using SSMS 2008 to execute Display Estimated Query Plan (Ctrl+L), it will show you if you’re missing any indexes. This will even work if you connect SSMS 2008 to SQL 2005!

Missing Index

Missing Index

That pretty much covers it for now. HTH! :)

Michelle

BCP Basics

In this blog post, I’m going to walk through the basics of BCP (bulk copy program). BCP is a utility that installs with SQL Server and can assist with large data transfers.

Let’s see what parameter options are available to use. From the command line on a machine with SQL Server installed, type “bcp” and press Enter.

BCP Parameters

You can find out more information on BCP parameters on Books Online: http://msdn.microsoft.com/en-us/library/ms162802.aspx

For now, we’re going to examine just the basics. The simplest syntax of a BCP command is:

bcp
databaseName.Schema.TableName *or* “Query”
in, out, *or* queryout
-S ServerName\instanceName
-U userName -P password *or* -T
-c *or* -n *or* specify storage information for each column

Let’s look at these options in a little more detail:

databaseName.Schema.TableName *or* Query
You can specify either an entire table to copy or a query. The query should be surrounded in quotations and must also include the fully qualified table name.

in, out, *or* queryout
in = import, out = full table export, queryout = query to select data for export

-U userName -P password *or* -T
You can either specify a specific account to access SQL Server, or use -T to indicate Trusted Connection (i.e. Windows Authentication)

-c *or* -n *or* specify storage information for each column
-c indicates character data type, -n indicates native data type; if neither one is specified, by default you will be prompted for the data type for each column.

Now let’s put this together and run some BCP commands. All of these examples will use the AdventureWorks 2008 sample database.

First, let’s export an entire table. To do this, we’ll use the “out” parameter.

bcp AdventureWorks.Sales.SalesOrderDetail out
C:\bcp_outputTable.txt -SYourServerName -T -c

 

Export Table with BCP

Export Table with BCP - Results

I don’t normally export an entire table… or at least, not in one process. So let’s walk through what it would look like to export the same table using a query. This will use the “queryout” parameter.

 

bcp "Select SalesOrderID, SalesOrderDetailID, OrderQty, ProductID
From AdventureWorks.Sales.SalesOrderDetail" queryout
C:\bcp_outputQuery.txt -SYourServerName -T -c

 

Export Query with BCP

Export Query with BCP - Results

You’ll notice that the total duration for the query was shorter than for the full-table export. This is because we’re only exporting a few of the columns. This is important to keep in mind when bcp’ing data: you’ll get better performance if you only export the data elements that you actually need.

Now that we’ve exported some data, let’s walk through the process of importing this data. First, let’s create a table with a constraint that will result in some errors.

Create Table dbo.testBCPLoad
(
      SalesOrderID          int      Not Null
    , SalesOrderDetailID    int      Not Null
    , OrderQty              smallint Null
    , ProductID             int      Null
 
    Constraint PK_testBCPLoad
        Primary Key Clustered
        (SalesOrderID)
);

Now execute the BCP import command:

bcp sandbox.dbo.testBCPLoad in
C:\bcp_outputQuery.txt -SYourServername -T -c

 

Load Data with BCP

Load Data with BCP - Error

You should receive a Primary Key error. When you check your results in SQL Server, you should find no results loaded into the table. This is BCP’s default behavior.

Check Destination Table

Let’s change our constraint and try the same BCP command again:

Alter Table dbo.testBCPLoad
    Drop Constraint PK_testBCPLoad;
 
Alter Table dbo.testBCPLoad
    Add Constraint PK_testBCPLoad
    Primary Key Clustered
        (SalesOrderID, SalesOrderDetailID);

 

bcp sandbox.dbo.testBCPLoad in
C:\bcp_outputQuery.txt -SYourServername -T -c

You should now have the data loaded into your SQL Server destination table:

Import Data with BCP - Results

Destination Table

So there you have it, the basics of BCP! :)

A few BCP tips:

  • BCP commands are case-sensitive!
  • If you’re accessing the data across a WAN, perhaps via a VPN connection, try to remote desktop (mstsc) to the actual SQL Server to perform the BCP. If possible, keep the operation on the same local drive or even local network as the server; the less distance data needs to travel across a network, the faster BCP will perform.
  • If you need to copy large amounts of data (i.e. >100mm rows), try breaking the data into smaller chunks. This will help if you have an error during BCP (i.e. a PK error can rollback the entire import operation by default, although there are options that can change this behavior). When working with partitioned tables, I find it very efficient to segregate the data imported/exported by partition.
  • If you’re BCP’ing data into a new table, you can minimize impact on the server by waiting to create your indexes after all the data is loaded.
  • I like to construct my queries in SSMS, then copy them to BCP. Since the command-line utility does not support copy and pasting, I create a text file with my BCP command in NotePad, then save the command as a .cmd. To execute, just call the .cmd file.

Replication Monitor

In my last blog post, I provided a script to view replication latencyIan Kirk took the script and ran with it, adding centralized execution and permanent logging. I’ve tweaked it a little bit further and deployed to production. So far, so good.

Here’s the latest and greatest for those interested:

If Object_ID('dbo.dba_replicationMonitor') Is Null
Begin
    Create Table dbo.dba_replicationMonitor
    ( 
          monitor_id            int Identity(1,1)   Not Null
        , monitorDate           smalldatetime       Not Null 
        , publicationName       sysname             Not Null
        , publicationDB         sysname             Not Null
        , iteration             int                 Null
        , tracer_id             int                 Null
        , distributor_latency   int                 Null
        , subscriber            varchar(1000)       Null
        , subscriber_db         varchar(1000)       Null
        , subscriber_latency    int                 Null
        , overall_latency       int                 Null 
    );
End;
 
If ObjectProperty(Object_ID('dbo.dba_replicationLatencyMonitor_sp'), N'IsProcedure') = 1
Begin
    Drop Procedure dbo.dba_replicationLatencyMonitor_sp;
    Print 'Procedure dba_replicationLatencyMonitor_sp dropped';
End;
Go
 
Set Quoted_Identifier On
Go
Set ANSI_Nulls On
Go
 
Create Procedure dbo.dba_replicationLatencyMonitor_sp
 
        /* Declare Parameters */
          @publicationToTest    sysname        = N'yourPublicationName'
        , @publicationDB        sysname        = N'yourPublicationDB'
        , @replicationDelay     varchar(10)    = N'00:00:30'
        , @iterations           int            = 5
        , @iterationDelay       varchar(10)    = N'00:00:30'
        , @displayResults       bit            = 0
        , @deleteTokens         bit            = 1
As
/*********************************************************************************
    Name:       dba_replicationLatencyMonitor_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves the amount of replication latency in seconds
 
    Notes:      Default settings will run 1 test every minute for 5 minutes.
 
                @publicationToTest = defaults to yourPublicationName publication
 
                @publicationDB = the database that is the source for the publication.
				    The tracer procs are found in the publishing DB.
 
                @replicationDelay = how long to wait for the token to replicate;
                    probably should not set to anything less than 10 (in seconds)
 
                @iterations = how many tokens you want to test
 
                @iterationDelay = how long to wait between sending test tokens
                    (in seconds)
 
                @displayResults = print results to screen when complete
 
                @deleteTokens = whether you want to retain tokens when done
 
    Called by:  DBA
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2008-11-20   MFU        Initial Release
    2008-11-24	 ILK        Tweaked to allow for centralized execution 
                            Replaced temp table with permanent table.
    2008-11-25   MFU        More tweaking, added publication data to 
                            dba_replicationMonitor, fixed NULL latency data,
                            moved dba_replicationMonitor creation out of proc
*********************************************************************************
    Exec dbo.dba_replicationLatencyMonitor_sp
          @publicationToTest    = N'myTestPublication'
        , @publicationDB        = N'sandbox_publisher'
        , @replicationDelay     = N'00:00:05'
        , @iterations           = 1
        , @iterationDelay       = N'00:00:05'
        , @displayResults       = 1
        , @deleteTokens         = 1;
*********************************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    /* Declare Variables */
    Declare @currentIteration   int
          , @tokenID            bigint
          , @currentDateTime    smalldatetime
          , @sqlStatement       nvarchar(200)
          , @parmDefinition		nvarchar(500);
 
    Declare @tokenResults Table
    ( 
          iteration             int             Null
        , tracer_id             int             Null
        , distributor_latency   int             Null
        , subscriber            varchar(1000)   Null
        , subscriber_db         varchar(1000)   Null
        , subscriber_latency    int             Null
        , overall_latency       int             Null 
    );
 
    /* Initialize our variables */
    Select @currentIteration = 0
         , @currentDateTime  = GetDate();
 
    While @currentIteration < @iterations
    Begin
 
		/* Prepare the stored procedure execution string */
		Set @sqlStatement = N'Execute ' + @publicationDB + N'.sys.sp_postTracerToken ' + 
							N'@publication = @VARpublicationToTest , ' +
							N'@tracer_token_id = @VARtokenID OutPut;'
 
		/* Define the parameters used by the sp_ExecuteSQL later */
		Set @parmDefinition = N'@VARpublicationToTest sysname, ' +
			N'@VARtokenID bigint OutPut';
 
        /* Insert a new tracer token in the publication database */
        Execute sp_executesql 
              @sqlStatement
            , @parmDefinition
            , @VARpublicationToTest = @publicationToTest
            , @VARtokenID = @TokenID OutPut;
 
        /* Give a few seconds to allow the record to reach the subscriber */
        WaitFor Delay @replicationDelay;
 
        /* Prepare our statement to retrieve tracer token data */
        Select @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_helpTracerTokenHistory ' +
                    N'@publication = @VARpublicationToTest , ' +
                    N'@tracer_id = @VARtokenID'
            , @parmDefinition = N'@VARpublicationToTest sysname, ' +
                    N'@VARtokenID bigint';
 
        /* Store our results for retrieval later */
        Insert Into @tokenResults
        (
            distributor_latency
          , subscriber
          , subscriber_db
          , subscriber_latency
          , overall_latency
        )
        Execute sp_executesql 
              @sqlStatement
            , @parmDefinition
            , @VARpublicationToTest = @publicationToTest
            , @VARtokenID = @TokenID;
 
        /* Assign the iteration and token id to the results for easier investigation */
        Update @tokenResults
        Set iteration = @currentIteration + 1
          , tracer_id = @tokenID
        Where iteration Is Null;
 
        /* Wait for the specified time period before creating another token */
        WaitFor Delay @iterationDelay;
 
        /* Avoid endless looping... :) */
        Set @currentIteration = @currentIteration + 1;
 
    End;
 
    /* Display our results */
    If @displayResults = 1
    Begin
        Select 
              iteration
            , tracer_id
            , IsNull(distributor_latency, 0) As 'distributor_latency'
            , subscriber
            , subscriber_db
            , IsNull(subscriber_latency, 0) As 'subscriber_latency'
            , IsNull(overall_latency, 
                IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
                As 'overall_latency'
        From @tokenResults;
    End;
 
    /* Store our results */
    Insert Into dbo.dba_replicationMonitor
    (
          monitorDate
        , publicationName
        , publicationDB
        , iteration
        , tracer_id
        , distributor_latency
        , subscriber
        , subscriber_db
        , subscriber_latency
        , overall_latency
    )
    Select 
          @currentDateTime
        , @publicationToTest
        , @publicationDB
        , iteration
        , tracer_id
        , IsNull(distributor_latency, 0)
        , subscriber
        , subscriber_db
        , IsNull(subscriber_latency, 0)
        , IsNull(overall_latency, 
            IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
    From @tokenResults;
 
    /* Delete the tracer tokens if requested */
    If @deleteTokens = 1
    Begin
 
        Select @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_deleteTracerTokenHistory ' +
                    N'@publication = @VARpublicationToTest , ' +
                    N'@cutoff_date = @VARcurrentDateTime'
            , @parmDefinition = N'@VARpublicationToTest sysname, ' +
                    N'@VARcurrentDateTime datetime';
 
        Execute sp_executesql 
              @sqlStatement
            , @parmDefinition
            , @VARpublicationToTest = @publicationToTest
            , @VARcurrentDateTime = @currentDateTime;
 
    End;
 
    Set NoCount Off;
    Return 0;
End
Go
 
Set Quoted_Identifier Off;
Go
Set ANSI_Nulls On;
Go

 

Note: All of my stored procedures have standardized error handling that I remove before posting to avoid confusion; you may want to implement your own error handling.

Checking Replication Latency with T-SQL

This post may only appeal to small subset of DBA’s. :)

This particular script was born of a need to view replication latency in production. I had to investigate whether the replication latency issues we were experiencing in production were occurring on the publisher (us) or the subscriber (them), and address accordingly. “Why just not use Replication Monitor?”, you might ask. Good question! I would definitely use Replication Monitor if it were available to me; however, I do not have the necessary permissions to the Distributor. :(

Fortunately, SQL 2005 provides us with some tools to help: sp_postTracerToken and sp_helpTracerTokenHistory. This allows us to access the same Tracer Tokens feature that is in Replication Monitor.


Replication Monitor - Tracer Tokens

 

So that’s the backstory, and here’s the script I came up with. For those interested, it’d be pretty easy to modify this script to use a permanent table and regularly log replication latency.

Create Procedure dbo.dba_replicationLatencyGet_sp
 
        /* Declare Parameters */
          @publicationToTest sysname        = N'yourPublicationName'
        , @replicationDelay  varchar(10)    = N'00:00:30'
        , @iterations        int            = 5
        , @iterationDelay    varchar(10)    = N'00:00:30'
        , @deleteTokens      bit            = 1
        , @deleteTempTable   bit            = 1
As
/*********************************************************************************
    Name:       dba_replicationLatencyGet_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves the amount of replication latency in seconds
 
    Notes:      Default settings will run 1 test every minute for 5 minutes.
 
                @publicationToTest = change the default to your publication
 
                @replicationDelay = how long to wait for the token to replicate;
                    probably should not set to anything less than 10 (in seconds)
 
                @iterations = how many tokens you want to test
 
                @iterationDelay = how long to wait between sending test tokens
                    (in seconds)
 
                @deleteTokens = whether you want to retain tokens when done
 
                @deleteTempTable = whether or not to retain the temporary table
                    when done.  Data stored to ##tokenResults; set @deleteTempTable 
                    flag to 0 if you do not want to delete when done.
 
    Called by:  DBA
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2008-11-20   MFU        Initial Release
*********************************************************************************
    Exec dbo.dba_replicationLatencyGet_sp
          @publicationToTest    = N'yourPublicationName'
        , @replicationDelay     = N'00:00:05'
        , @iterations           = 1
        , @iterationDelay       = N'00:00:05'
        , @deleteTokens         = 1
        , @deleteTempTable      = 1;
*********************************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    /* Declare Variables */
    Declare @currentIteration   int
          , @tokenID            bigint
          , @currentDateTime    smalldatetime;
 
    If Object_ID('tempdb.dbo.##tokenResults') Is Null
    Begin
        Create Table ##tokenResults
                        ( iteration           int             Null
                        , tracer_id           int             Null
                        , distributor_latency int             Null
                        , subscriber          varchar(1000)   Null
                        , subscriber_db       varchar(1000)   Null
                        , subscriber_latency  int             Null
                        , overall_latency     int             Null );
    End;
 
    /* Initialize our variables */
    Select @currentIteration = 0
         , @currentDateTime  = GetDate();
 
    While @currentIteration < @iterations
    Begin
 
        /* Insert a new tracer token in the publication database */
        Execute sys.sp_postTracerToken 
          @publication = @publicationToTest,
          @tracer_token_id = @tokenID OutPut;
 
        /* Give a few seconds to allow the record to reach the subscriber */
        WaitFor Delay @replicationDelay;
 
        /* Store our results in a temp table for retrieval later */
        Insert Into ##tokenResults
        (
            distributor_latency
          , subscriber
          , subscriber_db
          , subscriber_latency
          , overall_latency
        )
        Execute sys.sp_helpTracerTokenHistory @publicationToTest, @tokenID;
 
        /* Assign the iteration and token id to the results for easier investigation */
        Update ##tokenResults
        Set iteration = @currentIteration + 1
          , tracer_id = @tokenID
        Where iteration Is Null;
 
        /* Wait for the specified time period before creating another token */
        WaitFor Delay @iterationDelay;
 
        /* Avoid endless looping... :) */
        Set @currentIteration = @currentIteration + 1;
 
    End;
 
    Select * From ##tokenResults;
 
    If @deleteTempTable = 1
    Begin
        Drop Table ##tokenResults;
    End;
 
    If @deleteTokens = 1
    Begin
       Execute sp_deleteTracerTokenHistory @publication = @publicationToTest, @cutoff_date = @currentDateTime;
    End;
 
    Set NoCount Off;
    Return 0;
End
Go

Bulk Inserts with XML

Last week, I blogged about how to perform one-to-many inserts with table-valued parameters, a feature new in 2008. For those who do not yet have 2008 or will not have it in the near future, it may still be beneficial to use XML for bulk inserts.

Here’s a pretty simple example of how to accomplish this:

/* Create some tables to work with */
CREATE TABLE dbo.orders
(
      order_id      INT IDENTITY(1,1)   Not Null
    , orderDate     DATETIME            Not Null
    , customer_id   INT                 Not Null
 
    CONSTRAINT PK_orders
        PRIMARY KEY CLUSTERED(order_id)
);
 
CREATE TABLE dbo.orderDetails
(
      orderDetail_id    INT IDENTITY(1,1)   Not Null
    , order_id          INT                 Not Null
    , lineItem          INT                 Not Null
    , product_id        INT                 Not Null
 
    CONSTRAINT PK_orderDetails
        PRIMARY KEY CLUSTERED(orderDetail_id)
 
    CONSTRAINT FK_orderDetails_orderID
        FOREIGN KEY(order_id)
        REFERENCES dbo.orders(order_id)
);
Go
 
/* Create a new procedure using an XML parameter */
CREATE PROCEDURE dbo.insert_orderXML_sp
      @orderDate        DATETIME
    , @customer_id      INT
    , @orderDetailsXML  XML
AS
BEGIN
 
    SET NOCOUNT ON;
 
    DECLARE @myOrderID INT;
 
    INSERT INTO dbo.orders
    (
          orderDate
        , customer_id    
    )
    VALUES
    (
          @orderDate
        , @customer_id
    );
 
    SET @myOrderID = SCOPE_IDENTITY();
 
    INSERT INTO dbo.orderDetails
    (
          order_id
        , lineItem
        , product_id
    )
    SELECT @myOrderID
         , myXML.value('./@lineItem', 'int')
         , myXML.value('./@product_id', 'int')
    FROM @orderDetailsXML.nodes('/orderDetail') As nodes(myXML);
 
    SET NOCOUNT OFF;
 
END
GO
 
 
/* Call our stored procedure */
EXECUTE dbo.insert_orderXML_sp
      @orderDate = '2008-01-01'
    , @customer_id = 101
    , @orderDetailsXML = 
        '<orderDetail lineItem="1" product_id="123" />
         <orderDetail lineItem="2" product_id="456" />
         <orderDetail lineItem="3" product_id="789" />
         <orderDetail lineItem="4" product_id="246" />
         <orderDetail lineItem="5" product_id="135" />';
 
 
/* Check our data */
SELECT * FROM dbo.orders;
SELECT * FROM dbo.orderDetails;
 
 
/* Clean up our mess */
DROP PROCEDURE insert_orderXML_sp;
DROP TABLE dbo.orderDetails;
DROP TABLE dbo.orders;

I’ve found that this is more efficient when performing large parent/child inserts, i.e. 1 parent record to 100 child records. Keep in mind that there’s a point where doing an insert with XML is more expensive than using a traditional INSERT stored procedure. I haven’t run any tests yet to help define what that threshold is… more to come on this in the near future.

More on the Nodes() Method can be found here in Books Online: http://msdn.microsoft.com/en-us/library/ms188282(SQL.90).aspx

Update: I’ve just learned that the “value” keyword is case-sensitive. Apparently my code box plug-in was defaulting “value” to “VALUE.” :)

Here’s the error message you’ll get if you don’t have “value” in lower-case:
Cannot find either column “myXML” or the user-defined function or aggregate “myXML.VALUE”, or the name is ambiguous.