Difference between SQL 2005 and SQL 2008 for inserting multiple rows with XML

Question

I am using the following SQL code for inserting multiple rows of data in a table. The data is passed to the stored procedure using an XML variable :

INSERT INTO MyTable
   SELECT SampleTime = T.Item.value('SampleTime[1]', 'datetime'),
          Volume1 = T.Item.value('Volume1[1]', 'float'),
          Volume2 = T.Item.value('Volume2[1]', 'float')
   FROM @xml.nodes('//Root/MyRecord') T(item)

I have a whole bunch of unit tests to verify that I am inserting the right information, the right number of records, etc.. when I call the stored procedure.

All fine and dandy - that is, until we began to monkey around with the compatibility level of the database.

The code above worked beautifully as long as we kept the compatibility level of the DB at 90 (SQL 2005). When we set the compatibility level at 100 (SQL 2008), the unit tests failed, because the stored procedure using the code above times out.

The unit tests are dropping the database, re-creating it from scripts, and running the tests on the brand new DB, so it's not - I think - a question of the 'old compatibility level' sticking around.

Using the SQL Management studio, I made up a quick test SQL script. Using the same XML chunk, I alter the DB compat level , truncate the table, then use the code above to insert 650 rows. When the level is 90 (SQL 2005), it runs in milliseconds. When the level is 100 (SQL 2008) it sometimes takes over a minute, sometimes runs in milliseconds.

I'd appreciate any insight anyone might have into that.

---

EDIT

The script takes over a minute to run with my actual data, which has more rows than I show here, is a real table, and has an index. With the following example code, the difference goes between milliseconds and around 5 seconds.

--use [master]
--ALTER DATABASE MyDB SET compatibility_level =100

use [MyDB]

declare @xml xml

set @xml = '<?xml version="1.0"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  <Record>
    <SampleTime>2009-01-24T00:00:00</SampleTime>
    <Volume1>0</Volume1>
    <Volume2>0</Volume2>
  </Record>
..... 653 records, sample time spaced out 4 hours ........
</Root>'

DECLARE @myTable TABLE(
ID int IDENTITY(1,1) NOT NULL,
    [SampleTime] [datetime] NOT NULL,
    [Volume1] [float] NULL,
    [Volume2] [float] NULL)

INSERT INTO @myTable
    select 
        T.Item.value('SampleTime[1]', 'datetime') as SampleTime,
            Volume1 = T.Item.value('Volume1[1]', 'float'),
            Volume2 = T.Item.value('Volume2[1]', 'float')
    FROM @xml.nodes('//Root/Record') T(item)

I uncomment the 2 lines at the top, select them and run just that (the ALTER DATABASE statement), then comment the 2 lines, deselect any text and run the whole thing.

When I change from 90 to 100, it runs all the time in 5 seconds (I change the level once, but I run the series several times to see if I have consistent results). When I change from 100 to 90, it runs in milliseconds all the time. Just so you can play with it too. I am using SQL Server 2008 R2 standard edition.

Answer 1

REFERENCE - http://msdn.microsoft.com/en-us/library/bb510680.aspx

FOR SQL 2005

Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. Spatial indexes are not supported.

FOR SQL 2008
Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views, only physical consistency checks are performed by default.

FOR SQL 2005 If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on indexed views, XML indexes, and spatial indexes, where present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed.

FOR SQL 2008
When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the entire transaction is terminated and rolled back.

When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the behavior depends on the SET XACT_ABORT setting. If SET XACT_ABORT is OFF, a statement abort error generated by the DML statement using the OUTPUT clause will terminate the statement, but the execution of the batch continues and the transaction is not rolled back. If SET XACT_ABORT is ON, all run-time errors generated by the DML statement using the OUTPUT clause will terminate the batch, and the transaction is rolled back.