Setup upgrade to June CTP of SQL Server 2005 is unavailable. Typically, you would uninstall the existing build of SQL Server 2005, install the June CTP, and attach your database files to the server.

 

After Beta 2 of SQL Server 2005, we have made two changes that impact XML indexes. First, string comparisons on XML data type use a binary collation. Secondly, a bug in the key fields of the secondary XML indexes has been fixed. If you have created XML indexes using the Beta 2 build, you will find the XML indexes are disabled after the upgrade. If you have created the XML indexes using post Beta 2 builds, or already upgraded to a post Beta 2 build and rebuilt your XML indexes, you should not face this issue.

 

To rebuild your XML indexes, use the following steps:

 

1.       Check whether your XML indexes are disabled after the upgrade with the query below:

 

SELECT  count(*)

FROM    sys.xml_indexes

WHERE   is_disabled != 0;

 

If the query returns 0, then your XML indexes are not disabled and you do not need to do anything.

2.       Otherwise, enable (rebuild) the primary and secondary XML indexes. The primary XML indexes must be rebuilt before the secondary XML indexes

a.       Determine the primary XML indexes in a database:

SELECT  sxi.name "Index name", so.name "Table name"

FROM    sys.xml_indexes sxi JOIN sys.objects so

        ON (so.object_id = sxi.object_id)

WHERE   using_xml_index_id IS NULL;

 

b.       Rebuild the primary XML indexes:

ALTER INDEX <primary_xml_index> ON <table_name>

REBUILD;

 

c.       Determine the secondary XML indexes in a database:

SELECT  sxi.name "Index name", so.name "Table name"

FROM    sys.xml_indexes sxi JOIN sys.objects so

        ON (so.object_id = sxi.object_id)

WHERE   using_xml_index_id IS NOT NULL;

 

d.       Then rebuild the secondary XML indexes:

ALTER INDEX <secondary_xml_index> ON <table_name>

REBUILD;

 

A few additional notes:

 

·         The index options specified during the creation of these XML indexes will be preserved when you use ALTER INDEX … REBUILD.

·         Ana (tester) and Mark (developer) pointed out that a faster mechanism is to drop the XML indexes first and then to recreate them. This also requires less disk space, even compared to CREATE XML INDEX … WITH DROP_EXISTING. Do the following:

a.       Make a note of the index options using

SELECT * FROM sys.xml_indexes

 

b.      Drop the primary XML indexes – this will drop the secondary XML indexes as well

DROP INDEX <primary_xml_index> ON <table_name>

 

c.       Recreate the primary and secondary XML indexes with the desired index options.

·         Using ALTER INDEX ALL ON <table_name> REBUILD could be used – it figures out the order in which the primary XML indexes must be rebuilt. However, it rebuilds all the indexes on <table_name> – both XML and non-XML ones – and will take longer.

·         If you want to use SQL Server Management Studio to rebuild the XML indexes, see the topic “How to: Rebuild an Index (SQL Server Management Studio)” in SQL Server 2005 Books Online.

 

I hope this information helps you with Beta 2 to June CTP upgrade.

 

Thank you,

 

Shankar

Program Manager

Microsoft SQL Server

 

This posting is provided "AS IS" with no warranties, and confers no rights.

Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm