Overview of SQL Server 2005 for the Database Developer Page - 2

SQL Management Objects

The object model for SQL Server 2005 is the SQL Management Objects or SMO. This model is simple in conception and usage and is based on .NET Framework managed code. It is the primary tool for developing databases management applications using the .NET framework or the SQL server management studio operations. It replaces the SQL-DMO but supports it. About 150 new classes have been added to it. As a cached object model it allows changes to properties of an object before committing it to the SQL server.

This makes the model flexible and gives better performance. The instantiation of objects has also been optimized. Moreover, this model allows the establishment of multiple root directories for servers on a single collection. It implements advanced multi phase scripting. Finally the managed computer object simplifies the interface to the WMI and supports WMI monitoring and server configuration.

XML Technologies

Microsoft SQL Server 2005 suppports the use of XML through the Microsoft SQLXML. The user can convert relational data to an XML format or store XML data in a relational format. This support is provided by supporting XML as a first class data type and providing a distinct query language for XML. A number of new XML data types have also been introduced.—query(), exist(),value(), nodes() and modify(). These data types implement the XML Query(XQuery) specification. A number of keywords have been added for registering and managing XML schemas. Changes have also been made to FOR XML and OPENXML and they now support XML data type.

SQL server stores XML data as binary Large Objects and allows efficient reparsing, querying and compression in an internal representation. It also allows indexing down to the node level and supports both primary and secondary indexes on XML data. The existing optimizer engine is harnessed for using the indexes in appropriate queries on XML data.

Schema collections can also be associated with Columns of type XML and this provides the validations required for constraints, inserts and updates. This is also useful in typing of values inside stored XML data, query and in optimization and storage. A number of DDL statements also provide support for managing schemas on the server.

New Application Frameworks

The new application framework of SQL called the Service Broker is a distributed application framework that provides relable asynchronous messaging at the database to database level. It allows internal and external processes to exchange streams of reliable asynchronous messages using extensions of T-SQL DML.

The Reporting services is a major new component for integrated business intelligence. It is a server based platform for creating, managing and delivering traditional and interactive reports. It has everything required for creating out of the box reports, for hosting the reports, viewing them and embedding them in different solutions. The power of the Smart client and the Web application is brought to the service of the SQL server2005.

Notification services is a platform for developing and deploying applications that generate and send notices to users as personalized timely messages.

A number of new key features have been introduced into the SQL server mobile edition. The Mobile edition database can be created on the desktop or directly from SQL Server Management Studio. It can also be coded against the Data Transformation Services(DTS) objects. The SqlCeResult set that is derived from the SQLResult set allows mobile Edition to have true scrollable updateable cursors. It also allows binding to data objects on these devices. One or more applications can also be made to access a single data base on the mobile device. Notifications can be obtained on the status bar and the small size of the database can be maintained through an aggressive page reclamation policy. Parameteterized query can be developed using SQL syntax.

Language Enhancements

The T-SQL has been enhanced to include error handling, recursive query and to support the new database engine capabilities.

Two new relational operators have been introduced—PIVOT and UNPIVOT for use in the FORM clause query. They perform manipulation on an input table valued expression and output a table as a result.

The APPLY operator allows reference to a table valued function in a correlated sub query.

SQL Server 2005 introduces a simple yet powerful mechanism for exception handling. The TRY/CATCH mechanism is similar to .NET programming languages but is a T-SQL construct that captures errors at a statement level, batch level or transaction to terminate.

DDL enahancements allow users to send notifications to the Service Broker service asynchronously.

Full text search capabilities have been introduced along with cataloging capabilities for greater flexibility. Query performance and scalability have been improved and the new management tools have been provided to ensure full text implementation.

Security enhancements

The Trustworthy Computing initiative has contributed greatly to help Microsoft improve customer experience. A number of customer needs on security have been addressed.

1. Restricting user access to SQL server

2. Disabling services and restricting service configuration

3. Reducing the surface are aof attack for new features.

4. Native data encryption using certificate and key management system

The Microsoft SQL Server 2005 provides users and developers with a number of new features required for building secure database applications. It also removes barriers to code execution and storage. It integrates standards such as XML to open up new vistas in computing.