Last week I was engaged on SQL 2000 to SQL 2005 migration project, and we had identified some issues related to significant changes in execution plans.  Before migration we tested the application with using Upgrade Advisor (test passed without any caution), but hopefully we also had made an additional “trace replace” test. During this stage it was found that some stored procedures works fine in SQL 2000 but always failed in SQL 2005. Here is a script on “how to reproduce” :

declare @xmlDoc int

exec sp_xml_preparedocument @xmlDoc output, '<?xml  version=''1.0'' encoding=''windows-1251'' ?><filter><first-name></first-name><mid-name></mid-name><last-name>John</last-name><table-number></table-number><login></login><active>1</active></filter>'

select * into #temp_xml  from openxml(@xmlDoc, '/filter')

exec sp_xml_removedocument @xmlDoc

select a.attr_id, b.attr_val_id

from

  (

      select t1.id id, cast(cast(t2.text as varchar(10)) as int) attr_id

      from #temp_xml t1

            join #temp_xml t2 on t1.id = t2.parentid

      where t1.localname = 'AttributeID'

            and t2.localname  = '#text'

  ) a

      left outer join

  (

      select t1.parentid parentid, cast(cast(t2.text as varchar(10)) as int) attr_val_id

      from #temp_xml t1

            join #temp_xml t2 on t1.id = t2.parentid

      where t1.localname = 'AttrValueID'

            and t2.localname  = '#text'

  ) b on a.id = b.parentid

where a.attr_id is not null

order by a.attr_id

drop table #temp_xml

As mentioned above, SQL 2000 gives us an empty resultset, but SQL 2005 failed with

Msg 245, Level 16, State 1, Line 14

Conversion failed when converting the varchar value 'John' to data type int.

We compared execution plans,  and  it was found, that the following part has different execution rule for different SQL version.

select t1.id id, cast(cast(t2.text as varchar(10)) as int) attr_id

      from #temp_xml t1

            join #temp_xml t2 on t1.id = t2.parentid

      where t1.localname = 'AttributeID'

            and t2.localname  = '#text'

SQL 2000 gives us the following:

  |--Compute Scalar(DEFINE:([Expr1002]=Convert(Convert([t2].[text]))))

       |--Hash Match(Inner Join, HASH:([t1].[id])=([t2].[parentid]), RESIDUAL:([t2].[parentid]=[t1].[id]))

            |--Filter(WHERE:([t1].[localname]='AttributeID'))

            |    |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t1]))

            |--Filter(WHERE:([t2].[localname]='#text'))

                 |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t2]))

SQL 2005’s version:

  |--Hash Match(Inner Join, HASH:([t1].[id])=([t2].[parentid]), RESIDUAL:([tempdb].[dbo].[aaa].[parentid] as [t2].[parentid]=[tempdb].[dbo].[aaa].[id] as [t1].[id]))

       |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t1]), WHERE:([tempdb].[dbo].[aaa].[localname] as [t1].[localname]=N'AttributeID'))

       |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(int,CONVERT(varchar(10),[tempdb].[dbo].[aaa].[text] as [t2].[text],0),0)))

            |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t2]), WHERE:([tempdb].[dbo].[aaa].[localname] as [t2].[localname]=N'#text'))

As we can see SQL 2000 include two additional steps, but absolutely correct. SQL 2005 is much shorter, but absolutely incorrect and far from optimal (because of CONVERT applied to much more records than necessary). Tested with public 9.0.3159 version.

Summary: Upgrade advisor will never give you a 100% guaranty on safe migration. If possible please always collect some traces and replace it during testing phase of migration project.