Request for Feedback: Usage of xs:date and xs:dateTime and negative dates in SQL Server XM

Hi all
As part of the next version of SQL Server, we have to stop supporting
negative
years in the two types xs:dateTime and xs:date in the XML data type for
reasons I currently
cannot go into details.
The SQL Server product team would like your help in deciding on what upgrade
experience is acceptable. We would appreciate if you could do the following
if you are storing XML in your database that is constrained with an XML
schema collection (if you have customers, we would love to hear from their
databases, but please ask them first whether you can run the script):
1.. Consider the three upgrade options:
a. Database upgrade without size of data upgrade, affected tables, XML
Schema Collections and XML indexes will be taken offline. A built-in system
stored procedure will have to be run, that will perform the size of data
upgrade, change negative dates to a user-provided date that needs to be
positive and then recreates the indices and re-enables the tables and XML
Schema collections.
b. Database upgrade without size of data upgrade, affected XML indexes
will be taken offline. Creating an XML index and XQueries will fail if the
data queried/indexed contains a negative date or dateTime value. A built-in
system stored procedure will be provided, that for a given column will
perform the size of data upgrade, and change negative dates to a
user-provided date that needs to be positive. After this the query and index
creation can be run.
c. Database upgrade with size of data upgrade for every XML column that
can contain an xs:date or xs:dateTime value, affected XML indexes will be
taken offline. As part of the upgrade process, negative dates are mapped to
the smallest date 0001-01-01T00:00:00Z and the previous negative value will
be logged. Users then can recreate the XML indices.
2. run the attached script on your SQL Server database instance. Please
run it under an account that has the most data visibility. You can run it in
one of two ways:
A. run it to only gather metadata information. This is a short query
looking at the catalog views and does not impact the system performance in a
big way):
-- Just get the dependent columns per affected XML Schema Collection
exec DateTimeInvestigation 0
B. run it to gather metadata and data information. This may run a long
time
on the data and will most likely impact the system's performance, so please
run it only after making sure that this cost is acceptable:
-- Get the dependent columns per affected XML Schema Collection and
-- the number of affected values
exec DateTimeInvestigation 1
3. Please reply to this questionnaire by Monday, May 7 lunch time
providing your preferential ordering of 1.a to 1.c and attach the resulting
XML document from run of 2. Note that we are of course still taking
information from later runs but they may not be used for making this
decision if they arrive too late.
I and the rest of the SQL Server team highly appreciate your help with this.
Best regards
Michael Rys
Principal Program Manager
SQL Server Engine Team
(please remove the online. from the email address)
and here is the attached script.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ObKsOUgjHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all
> As part of the next version of SQL Server, we have to stop supporting
> negative
> years in the two types xs:dateTime and xs:date in the XML data type for
> reasons I currently
> cannot go into details.
> The SQL Server product team would like your help in deciding on what
> upgrade
> experience is acceptable. We would appreciate if you could do the
> following
> if you are storing XML in your database that is constrained with an XML
> schema collection (if you have customers, we would love to hear from their
> databases, but please ask them first whether you can run the script):
> 1.. Consider the three upgrade options:
> a. Database upgrade without size of data upgrade, affected tables, XML
> Schema Collections and XML indexes will be taken offline. A built-in
> system
> stored procedure will have to be run, that will perform the size of data
> upgrade, change negative dates to a user-provided date that needs to be
> positive and then recreates the indices and re-enables the tables and XML
> Schema collections.
> b. Database upgrade without size of data upgrade, affected XML indexes
> will be taken offline. Creating an XML index and XQueries will fail if the
> data queried/indexed contains a negative date or dateTime value. A
> built-in
> system stored procedure will be provided, that for a given column will
> perform the size of data upgrade, and change negative dates to a
> user-provided date that needs to be positive. After this the query and
> index
> creation can be run.
> c. Database upgrade with size of data upgrade for every XML column that
> can contain an xs:date or xs:dateTime value, affected XML indexes will be
> taken offline. As part of the upgrade process, negative dates are mapped
> to
> the smallest date 0001-01-01T00:00:00Z and the previous negative value
> will
> be logged. Users then can recreate the XML indices.
> 2. run the attached script on your SQL Server database instance. Please
> run it under an account that has the most data visibility. You can run it
> in
> one of two ways:
> A. run it to only gather metadata information. This is a short query
> looking at the catalog views and does not impact the system performance in
> a
> big way):
> -- Just get the dependent columns per affected XML Schema Collection
> exec DateTimeInvestigation 0
> B. run it to gather metadata and data information. This may run a long
> time
> on the data and will most likely impact the system's performance, so
> please
> run it only after making sure that this cost is acceptable:
> -- Get the dependent columns per affected XML Schema Collection and
> -- the number of affected values
> exec DateTimeInvestigation 1
> 3. Please reply to this questionnaire by Monday, May 7 lunch time
> providing your preferential ordering of 1.a to 1.c and attach the
> resulting
> XML document from run of 2. Note that we are of course still taking
> information from later runs but they may not be used for making this
> decision if they arrive too late.
> I and the rest of the SQL Server team highly appreciate your help with
> this.
> Best regards
> Michael Rys
> Principal Program Manager
> SQL Server Engine Team
> (please remove the online. from the email address)
>