Introduction

SQL Server 2005 provides more new language constructs and primitives for the T-SQL language than can be enumerated here. The enhancements to the T-SQL language reflect greater conformity to the ANSI-99 SQL specification and ease of use for developers. Many of the improvements in T-SQL are focused on greater expressiveness in queries. There are several new query types that allow for common scenarios to be covered in T-SQL code.

T-SQL in SQL Server 2005 provides new PIVOT and UNPIVOT operators which are very useful while generating the user defined reports. Previously developers have to write a huge code for converting rows into columns and vice versa. These operators perform a manipulation on an input table-valued expression and produce an output table as a result set. The PIVOT operator rotates rows into columns, optionally performing aggregations or other mathematical calculations along the way. It widens the input table expression based on a given pivot column and generates an output table with a column for each unique value in the pivot column. The UNPIVOT operator performs an operation opposite to the one PIVOT performs; it rotates columns into rows. The UNPIVOT operator narrows the input table expression based on a pivot column.

SQL Server 2005 introduces an excellent, simple but very powerful exception handling mechanism in the form of a TRY/CATCH T-SQL construct. This facility gives the developers to write more secured/managed code. Transaction abort errors that used to cause a batch to terminate can now be caught and handled.

In this article I am covering the key points to be remembered while performing migration from earlier versions of SQL Server to SQL Server 2005.

Migration Best practices - Upgrade options

We can upgrade an existing SQL Server 2000 database to SQL Server 2005 using the below methods:

1. Upgrade through Backup/Restore
2. Upgrade through Attach/Detach
3. Upgrade using Copy Database Wizard
4. Upgrade through an in-place install/upgrade

Compatibility

Once database is restored we need to set the compatibility to SQL server 2005. To achieve the compatibility we need to execute the below command

• sp_dbcmptlevel < restored database name >, 90

Points to remember - SQL Server 2005

• If the user database contains a user named "sys", it will not upgrade. "sys" is a reserved schema name in SQL Server 2005
• If you are using any object type code as “S” this will not work. System tables (S) are not exposed
• Updating system tables is not supported. Direct catalog updates are not supported in SQL Server 2005.
• Code will not work if it tries to GRANT, DENY, REVOKE, and DROP on system objects. Catalog and system objects are not modifiable.
• Code will not work if it accesses undocumented tables or columns

Usage of DBCC commands is not supported in SQL Server 2005.

List of Documented columns

Here is the list of documented columns from the SQL 2000 System tables that currently return NULL or 0 in Microsoft® SQL Server™ 2005:

• sysobjects.schema_ver
• sysindexes.first
• sysindexes.FirstIAM
• sysindexes.root
• sysindexes.minlen
• sysindexes.xmaxlen
• sysindexes.maxirow
• sysindexes.keys
• syscomments.compressed
• syscomments.ctext
• syslogins.password
• sysremotelogins.status
• sysoledbusers.rmtpassword
• sysdevices.size
• Avoid using columns that SQL Server Books Online describes as: “Reserved”, “For future use”, or “For internal use only”
• Use two-part names to refer to system objects. For example EXEC sys.sp_help Select * from sys.tables
• Avoid code that assumes all metadata is publicly visible
• By default, stored procedures run in caller’s execution context
• Use “EXECUTE AS OWNER” for owner’s execution context

GRANT VIEW DEFINITION if necessary

New Features in SQL Server 2005

Database Mirroring

Database mirroring is a solution to provide high availability at the database level as opposed to clustering which satisfies the need for high availability at the instance level. Maintaining synchronized copies of a database on two separate servers allows switching between them on an as needed basis allows automatic failover incase of failure of primary server.

Mirrored server can be used as report server for generating various reports by creating the snapshots on the mirrored database. Creating snapshots will not consume resources and are very fast while creating.

Note: Snapshots should be dropped once the purpose is completed, other wise snapshots will not allow you to restore database.

CLR Integration

Microsoft SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR). .Net CLR hosted inside SQL Server to improve performance, security and reliability. This enables developers to write procedures, triggers, and functions in any of the CLR languages.

If the procedure involves both significant data access and computation, consider separating the procedural code into a CLR portion that calls into a T-SQL procedure to perform data access, or a T-SQL procedure that calls into the CLR to perform computation. Another alternative is to use a single T-SQL batch that includes a set of queries that are executed once from managed code to reduce the number of round trips of submitting T-SQL statements from managed.

In previous releases of SQL Server, extended stored procedures (XPs) were the only alternative to T-SQL with which to write server-side code. CLR integration provides a more robust alternative to extended stored procedures (XPs).

T-SQL Enhancements

Most of SQL Server 2005's T-SQL enhancements focus on offering greater expressiveness to queries. The list of enhancements includes:

• Relational operators
• Changes to TOP
• New types of Exception handling
• XML formatted showplan
• Snapshot isolation level
• Recursive queries
• New Data types
• DDL triggers (You can use these triggers for Field auditing purpose)
• DML with output (access to Inserted and Deleted tables)

Partitioned Tables and Indexes

The ability to partition tables and indexes has always been a design tactic chosen to improve performance and manageability

in larger databases. Microsoft SQL Server 2005 has new features that simplify the design of using this feature. Please refer to this white paperfor a better understanding of partitions in SQL server 2005.

Reporting services

There are many features of Reporting Services that can be beneficial to customers. These features include centralized report storage and management, control over how reports are processed or administered, and the ability to quickly change report formats (HTML, Excel, PDF, etc.). With these capabilities, Reporting Services is valuable client application implementations

The main strength of Web-based reporting is the ability to make up-to-date information available to a distributed group of users. SQL Server Reporting Services is Microsoft's server-based reporting solution. While Reporting Services provides a way for people to find and view reports, the Reporting Services interface may not be the most appropriate one for all report users

Native Xml Web services

SQL Server 2005 provides a standard mechanism for accessing the database engine using SOAP via HTTP. Using this mechanism, you can send SOAP/HTTP requests to SQL Server to execute T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions with or without parameters.

Setting up SQL Server as a Web Service that can listen natively for HTTP SOAP requests requires creating an HTTP endpoint and defining the methods that the endpoint exposes. When an HTTP endpoint is created, it must be created with a unique URL that it uses to listen for incoming HTTP requests.

Providing SOAP/HTTP access enables a broader range of clients to access SQL Server, including "zero foot print" clients, because there is no longer a need to have a Microsoft Data Access Components (MDAC) stack installed on the client device trying to connect to SQL Server. It facilitates interoperability with .NET, SOAP Toolkit, Perl, and more on a variety of platforms. Since the SOAP/HTTP access mechanism is based on well-known technologies such as XML and HTTP, it inherently promotes interoperability and access to SQL Server in a heterogeneous environment. Any device that can parse XML and submit HTTP requests can now access SQL Server