Allen White

XML in SQL Server - a good idea but proceed with caution

I've had a really hard time trying to understand the real value of having an XML data type in SQL Server. Last week I taught the Microsoft class 2779B Implementing a Microsoft SQL Server 2005 Database, and struggled through the section on XML because I haven't been able to find a good use for it.

Last Thursday evening Dave Wilson of Progressive Insurance shared with the Ohio North SQL Server User's Group how Progressive is getting ready to implement loosely structured data - XML - in their applications. The problem is that every state has different data requirements for insurance reporting for every type of vehicle being insured. Another big problem is the rules that establish the data requirements change frequently. Maintaining relational structures to support the variety of data structures required has become a monumental task.

The team from Progressive went to Redmond to test their solutions and learned a lot about XML indexes and size requirements. The company has over a terabyte of data they have to maintain. Without an index on the XML data the data's useless, so they built a primary index (which shreds the XML and creates an internal representation of the data that SQL Server can search) and then three secondary indexes, a Path index, a Property index and a Value index. Once these were created the size on disk for the XML data went from 1TB to just under 9TB! They did a number of tests and found they could live with just the Property index, and the size on disk after that was 2.7TB.

It's good to see that Microsoft has provided a solid foundation for companies like Progressive to use XML for loosely structured data. It's also important to note the cost of implementing a solution like that. I hear comments that the Relational Model is dead because of things like XML. As is true in so many aspects of life, one tool does not solve all problems.

Allen

A New SQL Server MVP!

I received this email this morning:

Dear Allen White,

Congratulations! We are pleased to present you with the 2007 Microsoft® MVP Award!

The Microsoft MVP Award is our way of saying thank you and to honor and support the significant contributions you make to communities worldwide. As a recipient of Microsoft’s Most Valuable Professional award, you join an elite group of technical community leaders from around the world who foster the free and objective exchange of knowledge by actively sharing your real world expertise with users and Microsoft. Microsoft salutes all MVPs for promoting the spirit of community and enhancing people’s lives and the industry’s success everyday.

All I can say is that I'm humbled and greatful to everyone - my wife and daughter, my boss, Rich McCourt at Advanstar Communications, Microsoft reps John Miller and Bruce Szabo, the Ohio North SQL Server Special Interest Group, and the MCT community.

Also, the people who organize and manage PASS, the Professional Association for SQL Server for creating a fantastic community for networking and learning more about Microsoft SQL Server.

I also realize that I have a new responsibility, to keep working to help people understand the SQL Server environment so they can be most effective in their jobs. It's one I don't take lightly. (I just taught a two day SQL Server Security class this past week, and really enjoyed seeing my students "get it".)

Again, thanks.

Allen