Useful SQL Server scripts: 2007

Monday, December 17, 2007

Friday, March 23, 2007

For SQL Server, XML Is One Answer

For SQL Server, XML Is One Answer
Many folks describe XML as a method of separating the presentation of data from the management of the data. It is true that XML started there. However, XML is not a presentation format. It is a data communication format. The idea of separating data presentation from data management goes to a basic Object-Oriented application design, commonly called Model-View-Controller. For this design to scale, there has to be a way for the model (which manages the data) to statelessly communicate with the view (which presents it). XML was developed to be that format.
To make it work, XML had to be stateless, scalable, and yet easy to describe and understand. Therein lies its advantage. That advantage can be applied to other problems as well, some with more effects than others. So what makes XML so useful:
XML has the ability to represent a single complete transaction just as well as X12 EDI, but it is more readable and more flexible. Note: I did not compare XML to a comma-delimited file (CSV). They are not in the same league. CSV can represent a single record efficiently. XML can represent records in a dozen related tables, passing all of the data needed to convey the entire business intent of a record in a consistent manner. Where CSV can represent the information on a customer, XML can represent the customer, the sales order, the line items, the payment options, and the delivery mechanisms. Since XML formats can be easily defined and expanded, it is far easier to use XML for B-to-B communication than EDI.
Parsers for XML are free, widely available, and FAST. The MSXML parser, delivered for free with Internet Explorer 6.0, will not only parse the XML and verify the data against a user-provided standard (XML Schema), but it will execute code written in XSLT. This XML based data transformation language is open, cross-platform, and very good at what it does. Finally, developers can be freed from the drudgery of writing "yet another text parser" to read and write data files.
Any system can be augmented to read and write XML. Because of it's simplicity, it is fairly inexpensive for software vendors to add XML interfaces to their applications, or to export their data in XML format. This has made XML the most universally acceptable method for transferring data from one application to another, and it has happened at amazing speed. Of course, data communication entails a great deal more than just the data format. Fields and field values, types and constraints have to be defined, just as they were before. However, XML is a common format that can be read by a wide array of integration products and translated into other formats using GUI-based tools. Applications that use XML are far easier to integrate than ever before. As we are fast approaching the end of "useful stovepipe applications," no serious architect can design systems without the ability to either publish or consume software services using XML. Conversely, any existing application that cannot play in the XML world will quickly be dismissed as hopelessly antiquated or "not serious."
So many software vendors and application architects have seen the value of XML that an entire industry has developed around providing tools for reading, parsing, translating, routing, and managing XML documents. With so many tools available, a developer faced with the need to transfer data from one application to another would be foolish to consider spending time and effort to write components that produced or consumed data in any other format. It's just not productive to write code that someone has already written and tested, which both the developer and customer already have, and which is likely to be supported into the foreseeable future.
Certainly, XML is not good for all uses. Just as I'd be less than enthusiastic about SQL Stored Procedures filled with thousands of lines of procedural code, XML can be used in ways that are not efficient or even sensible. However, to point to a misuse of a technology and then criticize that technology because it failed to do well, is hardly a useful conversation.
OK, so XML has its place. But what about inside SQL Server? Why did Microsoft add XML capabilities to their flagship RDBMS system? It was the right thing to do.
While the value of SQL Server comes from it's exceptional qualities as a relational data system, companies need their programmers to be productive. It costs a huge amount of money to develop software, whether for new applications or for system integration. By providing XML interfaces in SQL Server, and providing XML tools and libraries to programmers, Microsoft has set the stage for a level of developer productivity that could not have been imagined just a few years ago.
Using XML based interfaces, software developers can avoid having to write lengthy and buggy "database layer" code, choosing instead to write code that simply loads and unloads data from objects to XML using built-in tools. Less code to write = less code to test, debug, and deploy = faster delivery of better code. That's a hard equation to beat. Additionally, adding XML capabilities to SQL Server doesn't detract from the strengths of the product. It's just as fast, scalable, and reliable as before.
Should SQL Developers become XML experts? That depends. Clearly, there are times when XML is very useful and cost efficient. Programmers and business users depend on data professionals to manage their data storage and retrieval systems, and to be familiar with best practices in data architecture, integration, and openness. I'd hate to be the SQL developer who recommended against XML because I failed to see how much money the business can save by using it.
_uacct = "UA-90206-6";
urchinTracker();
Copyright © 2002-2007 Red Gate Network. All Rights Reserved.

Monday, December 17, 2007

Monday, December 10, 2007

Thursday, December 06, 2007

Sunday, December 02, 2007

Thursday, November 01, 2007

Monday, October 29, 2007

Friday, October 26, 2007

Thursday, October 04, 2007

Friday, September 28, 2007

Monday, September 17, 2007

Friday, September 14, 2007

Tuesday, September 11, 2007

Tuesday, August 21, 2007

Monday, August 20, 2007

Thursday, August 16, 2007

Thursday, June 14, 2007

Wednesday, April 18, 2007

Thursday, March 29, 2007

Friday, March 23, 2007