Now that we have shipped SQL Server 2005, I would really like to hear from you what you want us to do regarding XML support in SQL Server 2005. Feel free to either contact me directly or leave your wish in the comment section.
Tomorrow I will be on my way to Europe for the SQL Server Launch Events in Zurich and Lausanne (Switzerland) and Athens (Greece). In Switzerland I will be presenting a specially made presentation on "What's new in SQL Server 2005" that will be more technical than the general launch presentation that I am giving in Athens.
In addition, I will be visiting family, speak at the .Net User Group in Kuesnacht on XQuery and XML in SQL Server 2005, and will give two presentations on SQL Server in general and XML in particular at the Swedish SQL Server User group in Stockholm on December 8.
If you are around, feel free to stop by and say hi.
Now that we have shipped SQL Server 2005, I am happy to see that people are interested in using it. Today, I ask you the following riddle:
What does SQL Server 2005, MySpace.com and Quiznos have in common?
Well, here is the answer:
Here is a link to Rick Penwell's blog on the MySpace next generation project. Why is that interesting you ask? Because MySpace.com is among the top 10 most visited websites in the world and because he will be using SQL Server 2005 and the XML datatype for the next generation! And btw, Rick: You are very welcome! And feel free to contact me with any questions, issues, feedback and requests (as long as it is not overwhelming my inbox :-)).
So what about Quiznos? Well, I don't know what they use in their IT department, but a co-worker of mine recently went to a Bellevue Quiznos for lunch. Here is an excerpt from his email (posted with permission):
I was at Quizno’s subs for lunch today and I was wearing a SQL Server shirt. The guy that takes the money asks if I work for SQL Server and tells me how much he likes SQL Server 2005, particularly the XML stuff.
Man! If a guy working in a sub shop knows what SQL Server 2005 XML is, and what he can do with it, then somebody has done a really good job!
Great! I just hope that this will enable him to work on something else than the check-out counter at a fast-food place :-). So do this guy a favor and develop on SQL Server 2005! :-)
So here you have it: SQL Server 2005, MySpace.com and Quiznos have (at least at some level :-)) the XML data type in common!
The Windows Server System team is doing a Learning and Help survey and need more SQL Server specific feedback.
If you live outside of the United States or Canada, we want to know more about your experience with SQL Server!
How did you initially learn to use SQL? What are your preferences for learning new topics?
What can we do to make SQL easier to learn and use?
Send e-mail to wssinfo@microsoft.com to obtain a secure code and link to the survey. Complete the survey for a chance to win a portable MP3 player!
(Please note the survey language in your mail: English, French, German, Spanish, Russian, Japanese, Korean, Chinese Simplified or Chinese Traditional).
---
In German:
Das Windows Server System team fuehrt eine Lern- und Hilfesystemumfrage durch und braucht mehr SQL Server spezifisches Feedback.
Falls Ihr ausserhalb der USA oder Kanada lebt, wollen wir mehr ueber Eure Erfahrungen mit SQL Server wissen!
Wie habt Ihr anfaenglich SQL gelernt? Wie lernt Ihr am besten neue Gebiete?
Wie koennen wir SQL einfacher zum Lernen und Benutzen vermitteln?
Sendet Email an wssinfo@microsoft.com, um den Sicherheitskode und den Link zur Umfrage zu erhalten. Fuellt die Umfrage aus und erhaltet die Moeglichkeit einen MP3 player zu gewinnen!
(Bitte gebt die bevorzugte Sprache in Eurer Email an: Englisch, Franzoesisch, Deutsch, Spanisch, Russisch, Japanisch, Koreanisch, Vereinfachtes oder Traditionelles Chinesisch)
Today is the big launch. Steve B. is giving the launch keynote in San Francisco. I am currently sitting in a meeting room at a competitor's place where I am at for a W3C XQuery meeting watching the live webcast during our lunch break :-). I already provided a list of many (but not all) of the new and improved features of SQL Server 2005. Today's launch has some additional cool tidbits:
On the XML side, we have no benchmarks yet (we should probably design some). But we have several both internal and external customers that built and deployed applications with SQL Server 2005's XML features. One of our internal sites replaced a 4000 line FOR XML EXPLICIT query with a 300 line FOR XML PATH query. Several of the early adopters of SQL Server 2005 already make use of the new XML datatype for storing documents. Some have documents that conform to their respective industry standard schemas. Others use XML and XQuery to process data that quickly changes its shape or is provided by their customers. Since I have not asked for their permission to be named on my blog, I probably better don't give out their names. I will see, if we can show-case one or two over the next couple of months if there is interest. In any case, such real world customer scenarios are in the end much more important than artificial benchmarks. And your feedback on performance and functionality will help us give you an even better experience in future releases.
Some other noteworthy SQL Server 2005 tidbits from the launch:
- The SQL Server 2005 Express Edition has a management tool! It is currently a CTP release, but mainly to be able to give it a beta cycle. It is available for download from the MSDN site for free (as are VS 2005 Express and SQL Server Express).
- About 50 hosting services will provide support for VS 2005 and SQL Server 2005 starting today. Maybe I should find some spare time to build something myself :-).
As to my launch involvement: I will be speaking at the two Swiss launch events later this month and I will also give the launch keynote in Athens, Greece on December 6th. On the way back I got invited to speak at and meet with the Swedish SQL Server user group in Stockholm. If you hang out in any of these areas, I am looking forward to meet you!
Finally, after 6 years of hard work, the W3C has released the Candidate Recommendation specs for XQuery 1.0, XPath 2.0 and XSLT 2.0. Does this mean that the recommendations are done? No, not yet. But it means that we reached an important milestone. Now everyone is officially invited to implement the specs, run the test suite, and provide feedback. Within the next couple of months, we will see which ones of the features will have at least two implementations with the same result and what may be underspecified or will not find an implementation.
Here are the specs:
We also release a new working draft for the XQuery 1.0 and XPath 2.0 Full-Text part:
As Michael Champion mentioned, the design by committee approach to standardization is sometimes slow, especially if it is somewhat feature-driven and not date-driven, and it is frought with compromise decisions, but I think the XQuery, XPath and XSLT specs are actually in pretty good shape and provide lots of useful functionality.
Since we now ship SQL Server 2005 with its XQuery subset and are planning the next release, I would like to encourage you to check out the XQuery specs (including the Full-Text specs) and let me know what functionality you want us to implement with the highest priority in the next release of SQL Server.
The PDC 2005 videos are now online (via http://blogs.msdn.com/mswanson/archive/2005/10/24/484434.aspx). Oleg has distilled the XML and language specific presentations.
And finally, from that list, here is the pointer to my presentation:
DAT405: SQL Server 2005: Deep Dive on XML and XQuery
Speaker: Michael Rys
Managing semi-structured and markup data is becoming an increasingly important aspect of data management. Besides storing XML natively in a database such as SQL Server 2005, it is as important to extract the information stored in the XML documents. This presentation dives into XQuery, the emerging standard to query and manipulate XML data; it discusses how SQL Server 2005 supports XQuery and XML DML; how to use these technologies to address typical scenarios and how to optimize for performance.
Download Presentation Download PowerPoint Download materials
We have RTM'ed today!
Today, we have released SQL Server 2005 and VisualStudio 2005 and the .Net Framework 2.0 to manufacturing. This means the code is baked, done, shipped,... :-). It will be downloadable from MSDN within the day and available through the other channels in a couple of days.
The following is just a teaser for all the cool things SQL Server 2005 will provide:
Enterprise Readiness:
- Multi-platform support: X32, X64 (WoW and native), IA64 Native, Numa Aware
- Multi-instance support with 7.0, 2000 and of course 2005
- Mobile, Tablet, Express (free), Workgroup, Standard and Enterprise – If we don’t have it, you don’t need it…
- Table Partitioning – partition by key easily – drop a partition and add a new one, no need to delete millions of rows
- Upgrade Advisor – (scan your existing 7.0 and 2000 servers and SP’s and get ready for an easy upgrade)
- SQL Server Management Studio – built in the VS IDE and able to manage all services from one UI
- Enhanced Full Text Search – Integrated backup/restore, thesaurus support, 100x faster for index build
- ONLINE: index build, page/file restore, concurrent log and db backup, memory add, fast recovery (online during rollback) – all operations on-line…
- Column level encryption
- Off-by-default – reduced attack space
- Code signing
- Vastly improved diagnostics – Watson dumps allow us to fix 80%+ of customer issues – NO CUSTOMER REPRO -, Dedicated admin thread, Dynamic Management Views (see current server activity)
- Database Snapshots – point-in-time query and reporting
- IPV6 support
- No more “general network error” – specific error messages for all errors.
- Automatic replication of schema changes
- Merge replication 2-5x faster
- Built-in Oracle to SQL Server replication – the only way to go… :-).
- HTTPS synch for mobile users
Integrated BI:
- BI Developer Studio – unique end-to-end integrated developer experience inside VS
- Report Builder – powerful new report design tool for end users with built-in auto drill-through
- UDM for AS – redefines OLAP and analytics
- Integration services (IS) – high performance and enterprise scale data integration and ETL pipeline
- Reporting Services in Express and Workgroup
- RS controls for VS – developers will love this.
- IS – support for web services and XML sources
- AS Proactive Caching – automatically process cubes when data changes in the RDBMS
- 10 New Data Mining algorithms – critical mass for data mining applications
- IS – support for data quality, data mining and text mining – going beyond ETL
- AS now supports multi-instance and integration with SQL profiler
- AS – new KPI framework for defining and displaying KPI’s – business analysts will love this feature.
Developer Productivity:
- T-SQL exception handling
- Service Broker – breakthrough for building loosely coupled web services applications via reliable messaging
- MARS – Multiple Active Result Sets (easier to migrate those Oracle and IBM apps to SQL Server)
- Snapshot Isolation – we have the most comprehensive set of isolation levels in the industry
- Query Notification and CacheSync – Integrated with ASP.Net for much improved performance and easier to code
- CLR Integration – Language choice - User defined types/aggregates, User defined procedures/triggers
- End to End XML – Datatype, XQuery, XML indexing, XSD typing, XML showplan, XML editor best in class, 2x client XML perf improvement
- Large Row support
- Persisted computed columns
- Distributed Query Tracing
- New Query Functions: pivot, unpivot, apply, except, intersect, TOP(n)
- Notification Services – easy to embed into new and existing applications
- SOAP/HTTP connectivity to the server
- ADO.Net tracing
- SNAC – no more MDAC versioning issues!!!
In addition, we have vastly improved the engineering tools and processes and quality testing over previous versions of SQL Server. For example, we've done 40x more stress testing hours that SQL Server 2000 and did countless security threat analysis.
We also did lots of customer testing. We provided 15 ISV upgrade labs around the world upgrading over 200 ISV applications to SQL Server 2005 BEFORE RTM!
All of our core business applications run on SQL Server 2005 today, over 100 applications, some of them multi-terabyte databases! Talk about "eating your own dogfood"!
We have over 50 customer applications in production today from all over the world (you will see them mentioned at the launch).
Thanks for all the feedback and help and see you at the launch events.
On my part, I am gearing up for the launch events in Switzerland and Athens, Greece where I will be presenting in late November/early December.
We are also already working on defining SP1 and the scope of the next releases. So let the suggestions, questions and feedback continue to roll!
It is my pleasure to introduce you all to Jinghao's weblog. He is our test lead and so far seems to cover the SQL Server 2005 XML support in Chinese :-).
Please go and take a look and say 你好.
Some people asked us to post Dave's (Campbell, not Letterman) humorous Top Ten Reasons to Upgrade that he showed in his keynote at SQLPASS. With his permission, here they are (note, if you cannot take a humorous stab against one of our competitors or our SQL Server 2000 fulltext support, please stop reading right now!).
10. So I can talk about something different next year at PASS.
9. No lines at our DMVs.
8. You'll love the new setup utility!
7. Hey, you got verbing to do; we got a new noun.
6. So Kalen and Itzik can publish the new “Inside” book(s)!
5. It’s been long enough for you to forget your SQL 2000 cluster upgrade experience.
4. You can use your watch instead of a calendar when telling your boss when the full text index rebuild will be done.
3. Because "Yu-kon".
2. Because you can only do the things in the next demo with SQL Server 2005.
and the number 1 reason is:
Friends don’t let friends drive Oracle.
:-)
Please send all flames to /dev/null.
The last year has been though on many. Many people have been and are getting killed in wars and conflicts (Iraq, Sudan/Dafour), got devastated by Tsunamis, starve due to famines and loose their lively-hood and sometimes even lives due to Hurricanes and Taifuns such as Kathrina and Stan. And now we have the devastating earthquake in the northern Pakistan/Indian/Afghanistan area. My thoughs and wishes are with all the people that have suffered greatly and I hope they will be able to rebuild.
I am glad that Microsoft is very generous in matching donations (and now even time) and that it has a yearly Giving Campaign (hint: it is currently on. MS employees: please consider giving [internal link]). I would recommend that everyone who can give, thinks about giving to at least one of the causes to help the affected and asks his or her employer to provide matching funds. As often, unless you can provide a specifically needed skill, it is often better to donate money.
Here is a list of some organizations that can benefit (feel free to add in the comment section, but please only list no-strings attached, well-regarded organizations):
And please do not forget the long-term issues (refugee issues, long-term famines, forgotten wars, inequality and injustices, the need for environmental protection of our biosphere) over the short term catastrophs.
Thanks.
In SQL Server 2005 versions up to the June CTP, the XML parser on the server-side defaulted to UTF-16 for documents without a leading byte-order mark and without an explicit encoding in the XML declaration. Since this was incompatible with the XML standard that defaults to UTF-8, we had reports of bugs where UTF-8 documents loaded through ADO.Net and SNCLI either got rejected - or worse - got accepted but stored wrongly since they were interpreted as UTF-16 encoded documents instead.
We have fixed this in the September CTP. The fixes(described below) unfortunately can lead to problems if you use SNCLI or ADO.Net versions that will be older than the version shipping with the September CTP and there are a few minor cases, where documents may be consumed differently and it may affect how you are dealing with XML documents returned from the server if you use certain client-side bindings using SNCLI. If you think you may be affected, please check out the list of fixes and possible impacts. Especially, if you think that you cannot upgrade the client components at the same time as the server.
Here are the fixes and possible impacts:
SNCLI (also known as SNAC):
- XML documents retrieved from the server through either DBTYPE_XML, DBTYPE_BYTES or ISequentialStream will continue to be encoded in UTF-16 but will have a BOM added automatically by the provider. If your client code currently adds the BOM, you will need to remove that code. Note: XML documents retrieved from the server through a character binding (such as WSTR) are not going to change and will continue to not have a BOM added.
- XML documents sent to the server through either DBTYPE_XML or DBTYPE_BYTES will have to have a BOM if they are encoded in UTF-16. If not, the parser will start to error, or if the XML document is a fragment that starts with a text node, it may parse incorrectly. OTOH, it will to start work correctly for UTF-8 encoded documents that have no XML declaration (as prescribed by the XML standard).
- XML documents sent to the server through a WSTR or BSTR character binding should NOT have a BOM. If they do, we will add an additional BOM and the provided BOM will be interpreted as a starting text node.
ADO.Net:
- XML documents sent to the server as a System.String value (SqlParamter.Value=stringValue) should NOT have a BOM. If they do, we will add an additional BOM and the provided BOM will be interpreted as a starting text node.
While we are less than a month away from the official launch week of SQL Server 2005, I am still providing you with the promissed change list for the XML area in SQL Server 2005's September CTP. And please accept my apology for the delay. Here is the previous CTP change list.
First, the ReadMe has a mistake. It claims
5.1.3 Documentation on Casting XML Data to a String or Binary Type Has Incorrect Information
In SQL Server 2005 Books Online, the "Serialization of XML Data" topic states that the syntax CONVERT(stringtype, xmlvalue, 1) can be used to avoid the entitization of white-space characters. This feature is not implemented in this CTP release.
This is a left-over from the June CTP ReadMe. This functionality is available starting with the September CTP.
Here is now the list of other items that I remember (there is always a chance that I miss a thing, the readme certainly did :-)). They include some changes and some clarifications/additions to Books Online.
Breaking Changes:
- Providers have changed how they transport differently encoded XML from client to server to minimize encoding/codepage conflicts.
As I have announced earlier in the beta newsgroups, we had to change the default parsing rules on the server to avoid problems with UTF-8 encoded documents. This necessitated some changes on how the providers transport the information. This posting contains more details.
- XQuery:
- Characters that are generated by CDATA sections in XQuery constructions are not considered to be ignorable white-space characters. This means that a presence of a CDATA section will always preserve the surrounding whitespace - regardless of the content of the CDATA section. For example,
select cast(''as xml).query('<a> <![CDATA[ ]]> {"abc"}</a>')
returns
<a> abc</a> (three spaces).
This has changed to be in alignment with XQuery and XML parsing rules.
- In an XML document that is constructed by using the query() method on the xml data type, a carriage return within a CDATA section becomes a line feed instead of staying as a carriage return. This has changed to be in alignment with XQuery and XML parsing rules.
- The namespace prefix xmlns identifies a namespace declaration attribute and cannot be redeclared in an XQuery expression. This has changed to be in alignment with XQuery's rules.
- Using 'for' with source expression () yields a static error instead of returning (). This is in alignment with the static error rules of XQuery.
- We fixed two static typing bugs: attributes within an xs:anyType-d section (xs:any, or an element of type xs:anyType) are now getting the correct type xs:anySimpleType. And atomization of such attributes now get the correct type xdt:anyAtomicType*.
- XML Schemas:
- An XML schema is rejected if maxInclusive is redefined in a derived type when base type has fixed="true".
- Support for list and union types: We had to disable support for unions containing lists.
Non-breaking Changes/Addendums to documentation:
- XQuery:
- Construction of empty elements and attributes is supported.
- The XQuery functions local-name() and namespace-uri() are now considered deterministic.
- As many requested: Conversion to SQL datetime in the value() method now supports non-UTC formats again, as in the following example:
declare @aaa xml
set @aaa = '<AAA MyDate="2005/1/1 00:00:00"/>'
select @aaa.value('(/AAA)[1]/@MyDate', 'datetime')
Note that support for some of the non-standard date/time formats may be locale dependendant. For example, 1/4/2005 may give different results depending on the default locale.
- The default SQL CONVERT style to SQL datetime now also supports the ISO8601 format with the Z timezone indicator:
select CONVERT(datetime, '2005-01-01T12:00:00Z')
This is a consequence of allowing the value() method change above.
- XML Schemas:
- The XML schema for Reporting Services (RDL) can now be loaded into an XML schema collection.
- Values of type xsd:dateTime and xsd:time that contain second values that have more than three fractional digits are rounded and do not return an error.
- Trailing spaces in minInclusive, minExclusive, maxInclusive, and maxExclusive facets are ignored in derived types that restrict the xsd:dateTime, xsd:data, and xsd:time data types.
- CONVERT(stringtype, xmlvalue, 1) can be used to avoid the entitization of white-space characters that we introduced earlier.
As always, feel free to contact me if you have any questions or concerns. I am not expecting any functional changes in the XML support between the September CTP and the final release anymore. So now is a perfect time to start building cool stuff!
Since we got "upgraded" from .Text to Community server, we are not getting good referrer log information anymore. Since I added some additional traffic tracking, I can again see a bit better, what people are looking for (still not as good as .Text).
And one of the most surprising aspects (beyond those that search my name for whatever reason...), is that quite a few queries search for something along the line of "sp_xml_preparedocument Failed load msxmlsql".
Now we had some problems in the April CTP of SQL Server 2005 with non-standard installations. But that was fixed and that problem could not affect the SQL Server 2000 SP4 release due to the different setup. Now it looks like people still seem to be running into some problem. Since we cannot repro it here at the "farm", I would like to ask you that, if have the problem, you please contact me with the following information:
- What version of SQL Server did you install?
- What SKU was it? Was it slipstreamed MSDE SP4, Enterprise RTM + SP4 etc.?
- Was it a fresh install or an upgrade?
- Please search your machine to find out whether or not MSXMLSQL.DLL and MSXMLSQL.RLL actually were installed and what path they were actually installed to.
- If you have used an unattended install file, please include a copy.
With your help, we can hopefully get to the bottom of this and reduce the searches for "failed to load msxmlsql".
I recently found a cool tracking website called
Extreme Tracking that gives me much better statistics than the Cummunity Server (which has much less useful referrer statistics than .Text had).
http://www.freewebsiteproviders.com/counters/extreme-tracking.htm has a short review. Please
let me know if you have any issues with it. Since I
am a cheapskate want to give my readers the ability to
take a look at the information themselves, I opted for the free version that allows others to check out the traffic info as well. Enjoy!
As many others in the SQL Server community, I have travelled to Grapevine, Texas to attend and present at this year's SQLPASS 2005 (site seems down while I write this). Since some people were unable to attend or had to leave early, I am now giving an additional presentation on Friday:
Friday, September 30
228: SQL Server 2005: Deep Dive Into XQuery
Michael Rys, Microsoft Corporation
4:00 p.m. – 5:15 p.m.; Texas 1-2
Note that this presentation is basically my PDC 2005 talk, that I will slightly adjust on the fly depending on how many people have attended the other XML and XQuery related talks. The goal is to make it complementary (while providing a bit of overlap for those that have not seen the other presentations).
Here is a quick overview of talks at SQLPASS that should be of interest in people that want to understand the XML support in SQL Server 2005:
Wednesday, September 28
208S: Managing Unstructured and Semistructured Data in SQL Server 2005
Michael Rys, Microsoft Corporation
4:30 p.m. – 6:00 p.m.; Texas AB [Update: Texas 3-4]
Thursday, September 29
210S: SQL Server 2005 XQuery Primer
Bob Beauchemin, SQLskills
10:15 a.m. – 11:45 a.m.; Texas 1-2
Friday, September 30
221: The 15 Need-to-Know Points About XML
Kent Tegels, DevelopMentor
1:00 p.m. – 2:15 p.m.; San Antonio 1-3
In addition, I give a SQLPASS Community Cabana on XQuery on Thursday at 2PM where people can come and ask questions about XQuery and where I will give a small tutorial on XQuery, if they have not all already attended Bob's talk on XQuery in the morning.
Another interesting talk will be Alan Griver's talk about LINQ and friends and VB:
Wednesday, September 28
212M: Future Directions in Data Language Integration
Alan Griver, Microsoft Corporation
1:30 p.m. – 2:45 p.m.; Texas 1-2
212M: Future Directions in Data Language Integration
Our VLDB 2005 paper on the innards of our XQuery implementation is now available online. Shankar gave the presentation in Trondheim. As the previous conference papers, this paper gives an in-depth look into how we implement our XML and XQuery support. A must-read for anyone that wants to understand the "how we do it" and the why.
From the abstract:
This paper describes the experiences and the challenges in implementing XQuery in Microsoft’s SQL Server 2005. XQuery language constructs are compiled into an enhanced set of relational operators while preserving the semantics of XQuery. The query tree is optimized using relational optimization techniques, such as cost-based decisions, and rewrite rules based on XML schemas.
Enjoy!
The friendly folks of the F5 Networks Dev Central team interviewed me at PDC 2005. The 5 minute interview is now
online (please accept my apologies for the rambling, but I seem to be recovering from the attendee party :-)).
The XML Query and XSL Working Groups have released the following Working Drafts of XML Query 1.0, XSL 2.0, XPath 2.0 and supporting documents. The goal of this release is to permit public review of changes made in response to Last Call comments. These documents are very close to the so called Candiate Recommendation that we hope to be able to publish soon. Yeah! We are making progress! Even though my issue with the spec has been closed without making the proposed change (minority opinion, here I come)...
Also published are the following updated Working Drafts which have not yet reached Last Call:
Comments are as always welcome through the W3C's Bugzilla interface or the public comment mailing list.
PS: Thanks to the editors and especially Norm who again did a terrific job on getting the docs ready...
Waehrend TechEd 2005 in Amsterdam hatte ich das Vergnuegen, mich mit Martin Szugat zu unterhalten. Das Interview ist jetzt veroeffentlicht. Ausserdem hat die neueste Ausgabe des dotnet Magazins SQL Server 2005 inklusive XML Support zum Schwerpunkt (http://www.aboutxml.de/xml-corner.zip [10MB ZIP Datei]). Viel Spass!
At TechEd 2005 Europe, I had the pleasure to chat with Martin Szugat. The interview is in German and is now published. Also, the latest dotnet magazine issue contains lots of info about SQL Server 2005 including its XML support (http://www.aboutxml.de/xml-corner.zip [10MB ZIP file]). Enjoy!