MS SQL Server 2005

MS SQL Server has been around for a while now and is quite popular in the enterprise database area. The current version, SQL Server 2000, was released about four years back and continues to be used in both enterprise and Web applications.

SQL Server 2005 is the new version of this database server and is expected to be released sometime in 2005. There are tons of exciting features in SQL 2005 that are very useful in enhancing developer productivity and increasing application performance. This article starts off a new series on SQL Server 2005 by introducing the new features in this new version. The series will continue in the following months with a detailed look at the new programming tools and technologies that are part of it.

One of the most important things to note about the database server is that it is rarely used on its own. Usually, it provides the back-end data support to a large application. For this purpose a large number of enhancements have been built into SQL 2005.

SQL Management Studio

SQL Server 2005 has a new tool called the SQL Server Management Studio. This is the all-in-one interface for performing any task related to SQL Server. You can create, modify objects (such as databases and tables), write SQL queries and even develop code right here.

Integrated .NET Framework

For the first time, the .NET framework is integrated into the SQL Server engine itself. This means that developers can easily start using the .NET class libraries and programming methodologies right from the database layer itself. The CLR integration is so strong that you can do a lot of things with it, including writing stored procedures and types with it! This series will take a detailed look at this useful feature.

Enhanced Transact-SQL

SQL Server's own version of the Structured Query Language called Transact-SQL (T-SQL) has been greatly enhanced in this version. Abilities such as error handling, Common Table Expressions, Recursive Queries and pivot operators have been added to easily manage the data within the database.

ADO.NET 2.0

Full support for ADO.NET 2.0's features in SQL 2005 include features such as query notification support (that informs the calling method whether there is change in a particular query's result set), MARS (Mutliple Active Result Sets) that gives the server the abilty to maintain more than one result set per open connection. And snapshot isolation that maintains 'versions' of the data in each row while a commit is taking place. This prevents locking blocks for read-only processes from occurring.

The new SQL 2005 management studio is a single interface for managing all SQL activities

SMO

SQL Management Objects are the new version of what was earlier known as SQL-DMO. This is a completely managed code equivalent that also adds in the new functionality of SQL Server 2005. The SMO and WMI APIs completely replace the
SQL-DMO.

Inherent XML support

SQL Server 2005 can work with XML everywhere. So much so that XML is actually a datatype in the server that can be assigned in table fields. XML is also completely manageable from both SQL-CLR and T-SQL using new operators that are added. XQuery support allows querying of values within XML data from SQL. SQL Server 2005 also acts as a Web service listener and provides HTTP Web service extensions without requiring IIS installed. So you can send queries and retrieve results in XML from the SQL 2005 Web service easily (provided you have permissions).

SQL Service Broker

This is a new application framework that allows the database server to provides secure and reliable asynchronous messaging at the database-to-database level. This messaging allows high level of scaling of huge workflow applications.

Reporting services

Used for Business Intelligence gathering, SQL Server Reporting Services is a server-based solution for creating, managing, viewing and publishing data-based reports. The service can pick up data from a variety of sources, including MS databases, Oracle and
XML.

Notification services

This is a method by which the database sends information to users on change of data. For instance, a user can set a notification request when the quantity of a particular product reaches a particular number. The database will automatically send this information to an e-mail address, SMS or Windows Messenger. The notifications can be data based or time based.

There are many more enhancements not discussed here that we'll look at as we continue this series. As it stands at the time of this writing, SQL Server 2005 is in Beta 2 with a new beta release planned soon. The final release should be expected sometime by the middle of the year. So keep reading and be ready for this new server when it comes your way.

Vinod Unny

Enterprise InfoTech