Request for Feedback: Usage of xs:date and xs:dateTime and negative dates in SQL Server XML Schema Collections
Hi all SQL Server and XML enthusiasts!
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):
- Consider the three upgrade options:
- 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.
- 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.
- 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.
- run the linked 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:
- 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
- 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
- Please reply to this questionnaire by Monday, May 7 lunch time PDT providing your preferential ordering of 1.1 to 1.3 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
mrys at the microsoft place.