Overview of SQL Server 2005 for the Database Developer

SQL Server 2005 Developer tools

The driving force behind Micorsoft SQL Servr 2005 is the process of integration. With MSSQL 2005 the database developer experiences a paradigm shift. He can now locate his code with refrence to its functionality, he can access data in its native formats or build complex systems that are server driven. The integration with the .NET framework gives him the power of the class library and modern programming languages to implement functionalities within the server.

The common language runtime(CLR) helps code procedures, functions and triggers in the .NET framework language of choice. The object oriented constructs of these languages, structured exception handling, arrays, namespaces and classes eases out the issues that confront him while programming with T-SQL. In other words the database server is extended to perform computation operations at the backend with ease.

The marriage between SQL Server and the CLR has brought with it a number of benefits. Some of the benefits that can be identified are:

1. Enhanced programming models

2. Enhanced safety and security

3. User defined types and aggregates

4. Common Development environment

5. Performance scalability

Managed code provides extensive support for processing numbers, complicated logic execution and stirng handling. The .NET class library provides the required functionality of thousands of prebuilt classes and routines that can be accessed from stored procedures, triggers and user defined functions.

String handling functions, math functions, date operations, advanced encryption algorithms, file access, image processing and XML data manipulations can be accessed from the managed stored procedure or functions or triggers and aggregates. Type safety is checked by the CLR before managed code is executed and this is an added advantage.

Choosing between Transact-SQL and managed code

The user has a choice of electing to use T-SQL or a programming language of the .NET framework for writing the stored procedures, triggers and user defined functions. T-SQL may be used in circumstances where the user wants to merely perform data access with almost no procedural logic. Computationally intense functions may demand the use of .NET Framework languages and the use of the .NET Framework class library.

However, T-SQL places managed code and data on the server and enables the user take advantage of the processing capabilities of the server and decreases the gap between data and the middle tier. The system is I/O bound and the CLR functions are available for the SQL Server query processor for parallelizing and optimizing execution. If processor intensive tasks are to be avoided on the server then code may be placed on the client.

Web services

Data access in SQL Server 2005 is centered around web services. The XML web services are developed in the database tier and SQL server becomes an HTTP listner. In Windows 2003 or Windows XP SP2 HTTP access can be used to accesss the SQL server directly. The HTTP Sys located within the operating system is a light weight web server.

The Web service interface now allows the execution of SQL statements and functions and procedures using the Web service interface. The Query is returned using XML format taking advantage of the infrastructure available in the Visual Studio.

ADO.NET and ADO.NET notification support

The new features of ADO.NET are:

1. Query change notifications

2. multiple active result sets

3. Native type support

4. Notification support for SQL Server queries.

The above features make for greater flexibility and scalability and commands can now be sent to the SQL Server to request that a notification be generated if the result set is at variance with the original result set procduced. The notifications are delivered through an asynchronous SQL Service Broker Queue and are useful for enabling the caching results in applications.

Multiple active result sets

SQL server facilitates users by providing them with a facility to have more than one result set pending request per connection. More than one default result set can be open per connection. These are forward only, read only result sets that are retrieved in large chunks to satisfy application requests without making round trips to the server. This removes the current restrictions of blocked drivers preventing the servicing of requests until an entire result set is consumed.

Native Data Type Support

A number of new data set types are now supported with the introduction of the new features in ADO.NET and integration with Visual Studio.

Snapshot isolation support

The new snapshot isolation support provided by SQL Server 2005 brings with it the following advantages.

1. Increased availability of data for read-only applications due to unblocked operations being allowed in the OLTP environment.

2. Automatic mandatory conflict detection for write transactions.

3. Simplified migration of applications from Oracle to SQL Server.

4. Support and exposure of snapshot isolation levels by a number of providers