SQL Server Engine

While the first section of this chapter looked at the bigger picture of new hardware support provided in SQL Server 2005, this next section will drill down into some the most important improvements that Microsoft has made to the SQL Server engine itself.

.NET Framework Integration

The most significant SQL Server engine enhancement in SQL Server 2005 is the integration of the Microsoft .NET Framework. The integration of the .NET Framework with SQL Server extends the capability of SQL Server 2005 by enabling the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages, such as VB.NET, C#, or J#. The integration of the .NET CLR with SQL Server 2005 is more than just skin deep, as the SQL Server database engine hosts the CLR in-process. You can learn more about the integration of the .NET Framework with SQL Server 2005 in Chapter 4.

Enhanced Multiple Instance Support

Another important enhancement found in the Enterprise Edition of SQL Server 2005 is support for up to 50 instances. This is up from the maximum of 16 instances that was supported in SQL Server 2000. This is a particularly significant improvement for hosting vendors who lease out multiple SQL Server services as part of their web services offerings.

New Data Types

SQL Server 2005 also supports several new data types. While the integration of the .NET Framework enables support for user-defined types, SQL Server 2005 also provides a couple of other new native data types: the varbinary(max) and XML data types. The varbinary(max) data type provides a new method for using LOBs with SQL Server. Unlike the older Image and Text data types, the new varbinary(max) data type can be used as a variable, and programmatically it can be treated like the smaller data types, allowing for easier and more consistent usage scenarios.

The new XML data type, based on the varbinary (max) data type, enables you to store XML documents in the database. However, unlike the varbinary (max) data type, which is essentially data agnostic, the new XML data type is designed expressly for XML data and supports schema verification of XML documents. You can learn more about the new data types supported by SQL Server 2005 in Chapter 7.

Database Snapshots and Mirroring

Database Mirroring protects against database failure by giving SQL Server 2005 an instant database standby capability. Database Mirroring is a database-level availability technology that works with all of the standard hardware supported by SQL Server. There’s no need for any shared storage between the primary server and the mirrored server, and there are no distance limitations. Database Mirroring works by sending transaction logs between the primary server and the mirroring server, basically making the new Database Mirroring feature a real-time log shipping application. Database Mirroring can be set up with a single database, or it can be set up for multiple databases.

Database Snapshots provide a read-only snapshot of a database at a specific point in time. Database Snapshots are best suited for creating copies of a database for reporting or for creating backup copies of a database that you can use to roll back a production database to a prior state. Database Snapshots can be combined with Database Mirroring to create a reporting server based on the data that’s on the mirrored server. The data on the mirrored server can’t be accessed directly because the mirroring server is always in recovery mode. However, you can create a Database Snapshot on the mirrored database and then access the database view for reporting. You can learn more about database views and mirroring in Chapter 3.

Native HTTP Support

One of the other significant improvements to the SQL Server database engine is the addition of native HTTP support to the engine itself. The capability of SQL Server to process incoming HTTP requests enables SQL Server to provide SQL statement execution and stored procedure invocation via the SOAP protocol. This means that SQL Server 2005 is able to process incoming web service requests without the presence of IIS or another web server. The new HTTP support gives SQL Server native HTTP listening capabilities, including the capability to support HTTP endpoints specifying the URL, port, and requests that will be supported. SQL Server is also able to publish web services as Web Services Description Language (WSDL) for endpoints. SQL Server’s HTTP support is standards-compliant, supporting SOAP 1.0 and 1.2, as well as WSDL 1.1. The new native HTTP support feature also supports both Windows and SQL Server authentication, as well as SSL. To enable this new feature to have greater compatibility with middle-tier programming, stored procedures can return result sets as an ADO.NET DataSet.

Server Events and DDL Triggers

SQL Server 2005’s new Server Event and DDL Triggers features enable you to programmatically respond to changes in the system. While both of these new features can accomplish similar functions, they are implemented quite differently. Like standard DML triggers, DDL triggers are synchronous events that execute stored procedures. You learn more about DDL triggers in Chapter 4.

In contrast, server events are asynchronous. In the server event model, the server posts an event to a SQL Broker Service, and then a consumer can independently retrieve that event. The event itself is recorded as XML data. There is no way to roll back an event, and an event can be ignored if no consumer retrieves it. When the event occurs, the system event is fired, which can notify you of the event or optionally execute a code routine. The following example illustrates the syntax used to set up an event notification:

CREATE EVENT NOTIFICATION MyDDLEvents ON SERVER FOR DDL_STATEMENTS TO SERVICE MyDDL_log

This example creates a new event, names the event notification MyDDLEvents, and attaches the event to the DDL statement. The TO SERVICE clause specifies that the SQL Broker Service named MyDLL_log will be the recipient of the events. You can find out more about the SQL Service Broker in Chapter 6.

Database Data File Enhancements

SQL Server 2005 now supports the ability to change the path of a database’s data and log files using the ALTER DATABASE command. SQL Server 2000 provided the ability to move the files for the tempdb database, but this wasn’t allowed for any other database. As you might expect, SQL Server 2005 supports moving the files only as an offline operation. The following example illustrates the new ALTER DATABASE statements syntax:

ALTER DATABASE <database_name>   MODIFY FILE(name=<'data_file_name'>, filename=<'new path'>) 

Data Partitioning

Another new enhancement that’s found in SQL Server 2005 is the ability to perform data partitioning. Data partitioning allows you to break a single database object such as a table or an index into multiple pieces. The new data partitioning feature facilitates the management of very large tables and indexes. The partitioning is transparent to the applications, which see only the database object itself and are unaware of multipart underlying storage, which is managed by SQL Server. Partitions can be created and dropped without affecting the availability of the database object itself. Essentially, partitioning enables you to split the underlying data store into multiple objects while still presenting a unified view of the object and all its partitions to an application. Figure 1-2 presents a basic overview of partitioning.

Figure 1-2: Data partitioning

SQL Server 2005 supports data partitioning for tables, indexes, and indexed views. The row is the basic unit of partitioning. The partitions can be created according to values found in the columns in a row. This is known as horizontal partitioning. For instance, a table might be partitioned by date, where a different partition is created for each year. This type of partitioning by date enables you to perform a sliding date window type of processing, where you can drop the partition containing data from last year and not affect access to data contained in the current year’s partition.

Data partitioning provides a couple of important benefits for very large databases (VLDBs). Data partitions can facilitate data management, enabling you to selectively back up only specified partitions. For example, in the case of a large table that is partitioned by date you may want to back up only the current year, not last year’s partition. Another advantage is that in multiprocessor systems, you can devote a CPU to processing its own partition for improved throughput.

There are two basic steps to implementing data partitioning. First, you need to determine exactly how you want to partition a given object. Second, you need to assign each partition to a physical storage location. The different partitions can all be assigned to a single filegroup or different partitions can be mapped to multiple filegroups.

The following example shows the syntax for creating a simple partition function and scheme that will partition a table using a Range partition:

CREATE PARTITION FUNCTION MyPF (int) AS RANGE LEFT FOR VALUES (50, 100) GO CREATE PARTITION SCHEME MyPS  AS PARTITION MyPF TO (FileGroup1) GO CREATE TABLE MyTable (col1 int, col2 varchar(50))   ON MyPS(col1) GO

The first line creates a partition function named MyPF. The (int) shows that the partitioning will be performed on a column that’s defined using the int data type. The keyword RANGE specifies that Range partitioning will be used. The LEFT keyword controls which partition will receive borderline values. The value of LEFT indicates that any row that has a value that matches the partition boundary will be moved to the partition immediately to the left. The VALUES clause is used to define the boundary points of the partitions. It’s important to note that these values are boundary points and not the partitions themselves. This will actually result in the creation of three partitions: the first will contain negative values to 50; the second partition will contain the values 51–100; the third partition will contain all values of 101 and over.

The second line creates a partition scheme named MyPS. The AS PARTITION clause is used to specify the partition function that will be used by this scheme. This example uses the MyPF partition function. The TO clause identifies the filegroup or filegroups that will store the partitions. This example uses a single filegroup, named FileGroup1.

Next, the partition scheme needs to be attached to the table that will be partitioned. This example shows the extended CREATE TABLE syntax that enables the table to be partitioned. The first part of the CREATE TABLE statement is unchanged. It specifies the table name, MyTable in this example, and the table’s columns. This simple table uses two columns, named col1 and col2. The new ON keyword is then used to specify the partition scheme that will be used. This example uses the MyPS partition scheme that was just created. And the column that contains the partition’s key data is supplied in parentheses. This example uses the column col1 for the partitioning key. This column is an int data type, which must match the data type specified in the partition function.

There are a few restrictions on the types of columns that can be used for the partitioning key. These restrictions are very similar to the limitation of columns that can be used in an index. The text, ntext, and image data types cannot be used. Likewise, timestamp columns are also restricted. Only native “T-SQL” data types can be used. You can’t use a user-defined type as a partitioning key. However, you can use the new varchar(max) data type. There is also a limitation of 1000 partitions per table, and all partitions must exist on a single node.

Index Enhancements

There are many new enhancements to indexes in SQL Server 2005. First, rebuilding a clustered index no longer forces all of the non-clustered index to be rebuilt. In SQL Server 2000, when you rebuilt a clustered index all of the related non-clustered indexes were rebuilt as well. That’s no longer the case, as SQL Server 2005 keeps the non-clustered indexes intact during the rebuild of the clustered index.

Next, there’s a new included columns feature that enables you to add non-key columns to an index. This new feature enables more queries to be covered by the index, thereby enhancing the performance of the queries by minimizing the need for the SQL Server engine to go to the underlying table to complete the query. Instead, the engine can satisfy the query requirements by using just the data in the covering index. One of the really nice aspects of the new included columns feature is the fact that the included columns that are not part of the key are not included in the maximum size of the index, which is still 900 bytes.

Another new index enhancement that Microsoft added to SQL Server 2005 is the ability to disable an index. Disabling an index stops that index from being maintained by the SQL Server engine and also prevents the index from being used. When an index is disabled, SQL Server deallocates the storage space used by the index but keeps the index’s metadata. Before a disabled index can be enabled again, it needs to be rebuilt using the ALTER INDEX command.

Online Index Operations

Prior versions of SQL Server didn’t allow any access to an index while that index was being rebuilt. You needed to wait until the rebuild process completed until the table could be updated again. SQL Server 2005’s new online index operations feature enables applications to access the index as well as perform update, insert, and delete operations on a table while the index rebuilding operation is running. You can find more information about SQL Server 2005’s online index operations in Chapter 3.

System Catalog and Metadata Enhancements

In SQL Server 2000 and earlier versions, the system catalog and metadata were stored as part of every database in the master database. With SQL Server 2005, this has changed and the metadata now resides in the resource database, which the system stores as a sys object. SQL Server 2005 no longer allows any direct access to system tables. This change has enabled better security and faster system upgrades by consolidating the system’s metadata. The catalog metadata is secured using row-level filters. You can learn more about SQL Server 2005’s row-level security in the later section “Security” in this chapter.

The new metadata is completely backward compatible as long as you haven’t used the undocumented system tables that Microsoft has repeatedly warned everyone not to use. The systems metadata in SQL Server 2005 is exposed through a set of catalog views. Catalog views, as well as ANSI INFORMATION_SCHEMA views, Property functions, and Built-in functions, replace the need to use system tables like you may have done in SQL Server 2000 . In all, there are over 250 new catalog views in SQL Server 2005, and they can be viewed from the sys schema of every user database. You can find the new system views by using the Microsoft SQL Server Manager Studio to open the Object Browser and then navigating to the Databases | <database> | Views | System Views node. You can also open a new query window and enter the following query:

select * from sys.system_views

Multiple Active Results Sets (MARS)

Previous versions of SQL Server were limited to one active result set per connection. SQL Server 2005 is now capable of supporting multiple active result sets on a single connection. This new feature enables you to open a single connection to the database, execute a query and process some results, and then later begin another query and process its results. Your applications can freely go back and forth between the multiple open results sets. Examples showing how you use the new MARS feature are presented in Chapter 4.

Bulk Data Loading

SQL Server 2005 provides some great improvements as well as performance increases in bulk data loading. The bulk data loading process now uses an XML-based format file that provides all of the functionality found in previous versions of the Bulk Copy Program’s (BCP) format file and more. Plus, the XML format makes the BCP format file easier to read and understand. For backward compatibility with existing applications, the old BCP format file can still be used.

SQL Server 2005’s bulk data loading process now supports logging of bad rows. This enables the bulk data loading process to continue even if invalid rows or data are encountered. Incorrectly formatted rows are written to an error file along with a description of the error condition. Rows that violate constraints are redirected to an error table along with their specific error condition.

Full-Text Search

Support for Full-Text search has also been enhanced in SQL Server 2005. Earlier versions of SQL Server required the use of stored procedures to create Full-Text search catalogs. With SQL Server 2005, several new DDL statements have been introduced to enable you to work with SQL Server’s Full-Text search features. For instance, two of the new T-SQL Full-Text search DDL statements are: CREATE FULLTEXT CATALOG and CREATE FULLTEXT INDEX.

Other enhancements to Full-Text search in SQL Server 2005 include the ability to back up and restore Full-Text search catalogs and indexes along with your database data. Likewise, Full-Text catalogs and indexes can be attached and detached with their corresponding databases. Another interesting enhancement in SQL Server 2005’s Full-Text search support is the ability to use a thesaurus to find synonyms of search words.

T-SQL Query Processor Enhancements

There are several enhancements to the query processor in SQL Server 2005, including Common Tables Expressions (CTE), an enhanced TOP clause, an enhanced WAITFOR statement, and a new OUTPUT clause for DML statements. Examples of using these enhancements in T-SQL are presented in Chapter 4.