New and Enhanced Subsystems
Native support for XML is another important development enhancement. SQL Server 2005 provides this support through the new XML data type and Web services. Other subsystem changes that aid development are found in SQL Server Service Broker and Notification Services.
XML and Web services. At a high level, SQL Server 2005 provides a new level of unified storage for XML and relational data, through the use of the new native XML data type. This data type provides support for native XML queries and strong data typing by associating the XML data type to an XML Schema Definition (XSD). XML support is tightly integrated with the SQL Server 2005 relational database engine and provides support for triggers on and replication, bulk load, and indexing of the XML data contained in the XML data type.
You can use the XML data type as a column in a table or as a variable or parameter in a stored procedure. You can use the type to store both typed and untyped data. When the data stored in an XML column has no XSD schema, the data is considered untyped. When an associated XSD schema exists, SQL Server will check the schema to make sure that the data store complies with the schema definition. In all cases, SQL Server 2005 checks data stored in the XML data type to ensure that the XML document is well formed. If the data isn't well formed, SQL Server will raise an error and won't store the data. The following example illustrates the creation of a table that uses the XML data type:
CREATE TABLE XmlData(
XmlID int NOT NULL,
XmlData xml NOT NULL)
The XML data type supports a maximum of 2GB of storage—the same amount as SQL Server allows for BLOB storage. The data's size and usage can have a big impact on the performance that the system can achieve while querying the XML data. To improve the performance of XML queries, SQL Server 2005 lets you create indexes over the columns that have the XML data type (although a clustered primary key must exist for the table). In addition, the OPENXML statement, which provides a rowset view over an XML document, has been enhanced to include support for the new native XML data types.
T-SQL in SQL Server 2005 also supports the XQuery language subset, which is based on the standard XPath query language and is used to query structured or semi-structured XML data. You can combine XQuery with T-SQL to query the data in an XML data type. The following example selectively retrieves the contents of an XML variable:
DECLARE @x xml
SET @x = '<ROOT><ID1>111</ID1>
<ID2>2</ID2></ROOT>'
SELECT @x.query('/ROOT/ID2')
Another important new XML-related feature is native HTTP Simple Object Access Protocol (SOAP) support. This feature lets SQL Server directly respond to the HTTP SOAP requests that Web services issue, without requiring a Microsoft IIS system as an intermediary. You can use this native HTTP SOAP support to create Web services that run on SQL Server 2005 and that can execute T-SQL batches, stored procedures, and user-defined functions. Like CLR support, this feature is turned off by default for security reasons. To enable HTTP support, you must first create an HTTP endpoint, as the code in Listing 4 shows.
Service Broker. An all new application-development subsystem, SQL Server 2005 Service Broker provides a framework for developing distributed, asynchronous line-of-business applications. Many other highly scalable applications—OS I/O subsystems, Web servers, even the SQL Server database engine's internal operations—support asynchronous queuing. Such support is an important factor for scalability because it lets an application respond to more requests than the platform might be able to handle physically. Service Broker's new asynchronous-queuing capability is built directly into the SQL Server engine and is fully transactional. Transactions can incorporate queued events and can be both committed and rolled back. You can access the Service Broker by using a set of new T-SQL statements including CREATE CONTRACT, CREATE QUEUE, CREATE MESSAGE TYPE, BEGIN DIALOG, SEND, and RECEIVE. You can find more information about the new Service Broker in "Scoping Out Service Broker," April 2004, InstantDoc ID 41887.
Notification Services. Notification Services began as a Web download for SQL Server 2000; SQL Server 2005 incorporates the feature into the core product. Notification Services lets businesses build rich notification applications that deliver personalized and timely information—such as stock market alerts, news subscriptions, package delivery alerts, and airline ticket prices—to any device. The feature is a software layer that connects an information source (i.e., event) and the intended recipient of that information. Notification Services applications consist of three basic components: events, subscriptions, and notifications. The application monitors certain predefined events and can filter and route information about those events to a variety of target devices, using a personalized delivery schedule. You can find more information about Notification Services at http://msdn.microsoft.com/sql/sqlwarehouse/notificationservices/default.aspx.
Client-side Development with ADO.NET 2.0
SQL Server 2005 provides important improvements for client-side coding, in the form of the new ADO.NET 2.0. As ADO.NET continues to mature, we're finally seeing features that were missing in its earlier version. Plus, ADO.NET 2.0 includes several brand new features, such as support for asynchronous queries, multiple active result sets (MARS), and a common connection model.
Asynchronous queries. Support for asynchronous queries was present in COM-based ADO but was missing in the earlier releases of ADO.NET. Asynchronous queries let client applications submit queries without blocking the application. New ADO.NET asynchronous support in the mid-tier layer of applications lets server applications issue multiple database requests on different threads without blocking the threads. The implementation is the same as other asynchronous operations in the .NET Framework. Start an asynchronous operation by using the object's BEGINxxx method and end it by using the ENDxxx method. Use the IAsyncResult object to check the completion status of the command. Listing 5 shows an example of ADO.NET 2.0's asynchronous support, which will also work with earlier versions of SQL Server, including SQL Server 2000 and SQL Server 7.0.
MARS. Earlier versions of ADO.NET were limited to one active result set per connection. COM-based ADO and OLE DB had a feature that let the application process multiple results sets, but under the hood that feature was actually spawning new connections to process the additional commands. ADO.NET 2.0's MARS support (which works only with SQL Server 2005) gives you the ability to execute multiple active commands on a single connection. MARS lets you open a connection to the database, open the first command and process some results, open the second command and process results, then go back to the first command and process more results—freely switching back and forth between the active commands. There's no blocking between the commands, and both commands share a single connection to the database. Listing 6 shows some sample code that uses MARS.
Common connection model. One of the problems with ADO.NET 1.0 was that you needed to use a specific provider to connect to a specific target database platform. For example, the SqlClient could connect only to SQL Server systems, not to Oracle systems. Likewise, the OracleClient could connect only to Oracle systems, not to SQL Server systems. Although you could build your code to load different database providers on the fly, doing so wasn't elegant, easy, or convenient. ADO.NET 2.0 solves this problem by adding a new Provider Factory capability that can instantiate the appropriate provider at runtime. The new Provider Factory classes are in the System.Data.Common namespace; the code in Listing 7 shows how to use them.
There are plenty more highpoints in ADO .NET 2.0. For instance, ADO.NET 2.0 supports all the new SQL Server 2005 data types, as well as a new Bulk Import and client failover. You can find more information about the new version at http://msdn.microsoft.com/library/en-us/dnvs05/html/ado2featurematrix.asp, or by reading "ADO.NET 2.0," February 2005, InstantDoc ID 44762.
Keeps Getting Better
It should be no surprise that SQL Server 2005 is chock full of new development features, and we've just covered the tip of the iceberg. Microsoft has also added an XML/A query language for Analysis Services, a System Management Objects (SMO) management API, and a SQL Server SQLCMD scripting shell—to name just a few. For more information about SQL Server 2005, or to download an evaluation copy, visit http://www.microsoft.com/sql.
End of Article
Prev. page
1
[2]
next page -->
