<SUBJECT>At a Loss</SUBJECT>

As I indirectly mentioned in my previous post, I will be giving a presentation on SQL Server 2005 at the March .NUFW meeting. I have been spending a great deal of time preparing for this presentation and have most recently been studying the new XML capabilities including extensions to the FOR XML clause, the inclusion of the XML data type, and the native support for XQuery.

All of these enhancements seem exciting but I am at a loss as to why much of this is being natively supported in an RDBMS, especially one that allows for embedded CLR assemblies. Don't get me wrong, I think that XML is a great technology and have used it many times but like everything else, it is a tool and needs to be used appropriately.

I fully understand wanting to manipulate XML as close to the database as possible to reduce the amount of network traffic typically required to build a dynamic XML document. As I mentioned above, SQL Server 2005 supports embedded CLR assemblies. One of the new features includes in version 2.0 of the .NET Framework is the SqlServer namespace that provides classes for querying and manipulating data against the context connection. This provides an easy and familiar mechanism for building and parsing XML documents inside of the database because the classes defined in the System.Xml namespace are accessible!

I even understand the need for an XML data type so that XML documents may be passed into or returned by stored procedures or user defined functions for parsing or building, respectively. What I do not understand is storing XML in a relational database!

All of the arguments I have read or heard for storing XML in a relational database fall into a couple of categories:

• XML is universal because it uses UNICODE.
• XML allows for native mapping of properties to objects, thus, it provides a simple mechanism for persisting and recovering the data related to an object.

These arguments have not convinced me that storing XML in a relational database is a good practice. The UNICODE argument falls apart immediately in that if UNICODE is needed, values can [typically] be stored natively in the database through the use of an NCHAR or NVARCHAR data type (SQL Server and Oracle support this). If natively storing the value as UNICODE is not an option, it can be converted later.

As for the second argument, I will agree that XML does allow for an easy mechanism for persisting and recovering object related data. This is also known as laziness! I would even argue that this approach is lazy even when the data is "guaranteed" (is anything ever certain?) not to change.

I have never seen an XML document that wasn't able to be easily represented in a relational format. It is true that many documents require many tables but the point is that it can still be done. In many aspects, it is also more efficient to store data values individually spread across multiple tables than it is to store a single XML document (or fragment) in a single column.

Parsing the data values into individual columns across an appropriate number of tables provides many advantages to storing an entire XML document in a single column.

• When storing raw XML, a great deal of space is wasted just to store tags and attribute names. Depending on the structure of the XML, this can quickly add up since it will be extended across all of the rows in the table storing the XML.
• What if a single value needs to be changed? How about adding a new Item? If the XML document is large, this can be an expensive operation since the entire column value will need to be replaced. (It should be noted here that SQL Server 2005 does support a proprietary XML Data Manipulation Language (DML) but the XQuery specification does not currently support this functionality so it is likely to change in future versions.)
• What if a different view of the data is needed? Suppose the XML represents an article and only the title is needed? The entire document would need to be read and parsed to extract a single value. The title itself could be broken out into its own column but then other problems may exist:
  1. Multiple copies of the same data if the title is left in the XML. This is wasting space and adding complexity because the values need to be kept in sync.
  2. The title could be removed from the XML and stored in its own column. This is eliminating the purpose of storing the XML in the database in the first place because the title will either need to be added back into the XML for delivery to the client or two queries will need to be run!
• Another gripe about storing raw XML in a database is that it is difficult to query against. An example is if the XML column stores documents that describe books and a user wants to find all of the books written by "Bob", either every column needs to be returned and searched using XPath or XQuery (SQL Server 2005 provides native support for XQuery but the syntax is an awkward combination of T-SQL and FLWOR)
• The XML specification does not require values to be strongly typed. Strongly typed data ensures consistency. Storing the values in individual columns that are appropriately typed will add an extra layer of protection to the data.
• Finally, a "typical" XML document does not represent a scalar value but rather, a complex type.

It is for these reasons that I do not support storing XML in a relational database even for document-centric type applications. As I mentioned earlier, I have no issue with XML itself. I believe that it is a great tool and deserves a place in every developer's toolkit. Like all tools though, it needs to be used appropriately in order to be effective. If my logic is flawed or I have missed something, please feel free to try to change my mind.

.NUFW Plug

The .NET User Group of Fort Wayne (.NUFW) meets on the second Tuesday of every month. Meetings are free and open to the public so if you are in the area and wish to attend, please stop by.

Upcoming Events

	2/8/2005	3/8/2005
Topic	Message Oriented Architecture	SQL Server 2005 (Yukon) Overview
Speaker	Mike Amundsen [INETA]	Dave Fancher

Please refer to the .NUFW Web site for meeting time and location.

GMail

I have four GMail invites if anyone is interested. Offer some constructive comments to one of my technical posts and an invite is yours! [First come, first serve but the invites will only be given if a relevant, thought provoking comment is posted]

A Matter of Great Importance

It has been a while since my last post. Work and the holidays have kept me pretty busy. During the past few weeks, I have had some time do so some of the reading that I have been meaning to do. Based on the content of the various books and journals I have been reading, it seems as though there is a great, ongoing debate as to which part of an application is more important: the user interface (UI) or the back-end code (for the purposes of this entry, I am including any data storage mechanisms and logic code including databases and stored procedures).

To me, this debate seems pointless. The answer to the question of "which is more important?" is really quite simple...both parts are equally important!

The equality of importance is apparent when one examines the true purpose of a program. The true purpose of a program is to make some task easier and more efficient for someone. It is extremely important to never lose sight of this purpose since doing so will negatively impact the quality of the end product.

Both parts of a program directly impact the overall user experience. Obviously, the UI is what the end user sees and interacts with. If the UI is cumbersome and/or difficult to learn, the task has not been simplified and may in fact have actually become more difficult. User interfaces must be intuitive.

On the other side of the coin, if the back-end performs poorly (including stability issues), the user becomes upset because although the task may have been simplified, it may not be more efficient. Even the perception of poorly written code will ultimately result in lower user satisfaction. Therefore, it is important to minimize the level of poorly written code in order to minimize areas of poor performance and the perception of poor performance.

The most elegant user interface ever designed cannot save an application with a poorly written back-end. The most efficient logic code ever written will not be used or appreciated if the user cannot figure out how to exploit it because of a cumbersome UI.

It is for these [brief] reasons that I believe that equal time should be spent on the design and implementation of both the user interface and back-end portions of any program. Only when all components have been well designed and developed will user satisfaction reach its maximum potential.

Caution: Education Ahead!

Ok, so I admit that I'm a little behind the curve on some of the technology that is available right now but all that is about to change. For my birthday, my wife presented me with a new Seagate 80GB hard drive which I am proud to say, installed without any problem. As I was installing the drive, I even decided that with the CD burner and a 256MB flash drive, I no longer needed the floppy drive installed so I'm finally running without a 3.5" drive on all of my computers.

The reason I asked my wife for a new hard drive is so I could set up my system for dual boot with a developer license of Windows Server 2003 Enterprise Edition and load other software such as SQL Server 2005 Beta 1 and Visual Studio 2005 Beta 2 so I could get myself up to speed on the upcoming technology. I don't have everything installed yet since I still have to download the MSDN portion of Visual Studio but what I have installed so far impresses me beyond my wildest dreams.

Even though I have been reading some about SQL Server 2005 tonight was my first exposure to it and therefore, it was my experience with SQL Server Enterprise Studio. I must say that so far I like what MS has done. The whole Visual Studio-like interface seems to be extremely intuitive and easy to use from what I have looked at. I know that I have barely scratched the surface of the new tools and I am also left wondering how many DBAs will like the extreme change.

In all honesty, tonight was also my first exposure to Windows 2003 Server and I am also impressed with it. I know that the product has been available for some time but I have not had access to any resource to dig in and fiddle around. I am looking forward to playing around with IIS6 and many of the other enhancements over Windows 2000 though I'm really primarily interested in SQL Server 2005.

As I was opening the box in preparation to install the new drive, I asked my wife if she realized that the present meant I was going to disappear into my home office more than I usually do since she was opening the door for me to install a shload of new "toys," She said she did but we'll see! As I learn more about SQL Server 2005 and Visual Studio 2005 I'll be sure to post it here.