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
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.
