Database Engine Programmability Enhancements
Microsoft SQL Server 2005 introduces several improvements and new features in the Database Engine that improve the capabilities and productivity of programmers. Database objects can be written in Microsoft .NET Framework-based languages such as Microsoft Visual C#. Development and debugging is integrated into the Microsoft Visual Studio environment allowing developers to use the same tools to develop database objects as they use to develop .NET Framework-based components and services. The Transact-SQL language has been extended with features such as improved error handling and support for recursive queries.
In addition, the data access interfaces used by applications to access instances of the Database Engine have been enhanced to increase programmer productivity. For more information, see Data Access Interfaces Enhancements.
Major enhancements to the Transact-SQL language are listed below. For additional information about new Transact-SQL features, see What's New and Enhanced in Transact-SQL (Transact-SQL).
Programmability Enhancements
Microsoft .NET Framework: Common Language Runtime Integration
The Microsoft .NET Framework common language runtime (CLR) is now hosted in the SQL Server Database Engine. This CLR integration environment supports procedural database objects, including functions, stored procedures, and triggers, that are written in .NET Framework-based languages such as Microsoft Visual C# and Visual Basic .NET. .NET Framework-based languages support logic and features not available in the Transact-SQL language, meaning more complex logic can now be incorporated in database objects. User-defined types and aggregates can also be written in .NET Framework-based languages to build more complex data types than were available in earlier versions of SQL Server.
The CLR Integrated programming environment is integrated into the Visual Studio development environment. Developers use the same tools for developing and debugging database objects as they use to develop client or middle-tier .NET components and services.
For more information, see Introduction to Common Language Runtime (CLR) Integration.
Microsoft .NET Framework: User-Defined Aggregates
User-defined aggregate functions written in a .NET Framework-based language extend the basic aggregate functions, such as SUM, MIN, and MAX, included in Transact-SQL. An aggregate function is one that processes several data values and returns a single value. Rather than retrieving an entire result set to an application and performing complex aggregate logic in the application, a .NET Framework aggregate function can perform the logic in the instance of the Database Engine and return only the final value to the application.
For more information, see CLR User-Defined Aggregates.
Microsoft .NET Framework: User-Defined Types
CLR user-defined types (UDTs) allow you to implement database data types in any of the languages supported by the .NET Framework CLR. CLR user-defined types are implemented as classes in a .NET Framework-based language and can have complex structures and behavior defined by the fields, methods, and properties of the class. CLR user-defined types are integrated with the Database Engine type system and can be used in all the contexts where the built-in types are used.
For more information, see CLR User-Defined Types.
Microsoft .NET Framework: Stored Procedures, Triggers, and User-defined Functions
Database procedural objects can be written in .NET Framework-based languages to incorporate more complex logic than is supported by the Transact-SQL language. Programmers develop the objects using the same language and development environment as they use to develop .NET Framework components and services.
For more information, see CLR Stored Procedures, CLR Triggers, CLR User-Defined Functions.
Web Access: Native HTTP SOAP Access
Instances of the Database Engine can be configured with HTTP endpoints or addresses to which SOAP requests can be sent from applications. The packets are received directly by the instance of the Database Engine without the need for configuring any middle-tier service such as a Microsoft Internet Information Services (IIS) server.
For more information, see Using Native XML Web Services in SQL Server 2005.
Transact-SQL Enhancements: New xml Data Type
The Database Engine introduces a new xml data type that supports storing XML documents and fragments in databases. XML instances can be stored in columns, stored procedure parameters, or variables created using the xml type.
For more information, see xml Data Type.
Transact-SQL Enhancements: FOR XML Enhancements
You can nest FOR XML statements to create a hierarchy of XML documents. The results of a FOR XML statement can be stored in a column, stored procedure parameter, or variable defined using the new xml data type.
For more information, see New FOR XML Features.
Transact-SQL Enhancements: Improved Error Handling
Programmers coding Transact-SQL batches, stored procedures, triggers, and user-defined functions can use familiar TRY-CATCH logic to trap and handle error conditions.
For more information, see Handling Database Engine Errors.
Transact-SQL Enhancements: New Metadata Views
The SQL Server 2005 Database Engine introduces two new sets of views for querying metadata. The system catalog views provide information about the objects, such as tables, stored procedures, and constraints, defined in SQL Server databases. Dynamic management views provide information about the current state of an instance of the Database Engine. Dynamic management views expose information from memory structures that are not persisted to disk, such as information about locks, threads, and tasks.
For more information, see Querying the SQL Server System Catalog.
Transact-SQL Enhancements: Ranking Functions
The Database Engine introduces a new category of functions called ranking functions. The functions that make up this category are:
- RANK
- DENSE_RANK
- NTILE
- ROW_NUMBER
These functions return a ranking value for each row in a partition.
For more information about a list of these functions, see Ranking Functions (Transact-SQL).
Transact-SQL Enhancements: Data Definition Language (DDL) Triggers
In addition to supporting triggers that are fired by data manipulation language statements that modify data (INSERT, UPDATE, and DELETE), the Database Engine includes a new class of triggers fired by DDL statements that modify database objects, such as the statements that start with CREATE, ALTER, or DROP. DDL triggers can be used to audit or regulate changes to a database schema.
For more information, see DDL Triggers.
Transact-SQL Enhancements: Event Notifications
A new kind of object, event notifications, can be programmed in the Database Engine. Event notifications send information about Transact-SQL DDL statements and SQL Trace events to a service broker service. Event notifications can be used to log and review changes or activity occurring on the database. They execute asynchronously and can offer a programming alternative to DML triggers, DDL triggers, and SQL Trace.
For more information, see Implementing Event Notifications.
Transact-SQL Enhancements: Queue Processing Extensions
Three Transact-SQL enhancements simplify building message queuing applications in loosely coupled, reliable, Internet-scale and business process automation systems. The INSERT, UPDATE, and DELETE now support an OUTPUT clause that return data based on the rows modified by the statement. READPAST can now be specified on UPDATE and DELETE statements, letting these statements skip queue rows held by other tasks and picking the next available row from a queue.
For more information, see OUTPUT Clause (Transact-SQL), and Locking Hints.
Transact-SQL Enhancements: Unified Large Object Programming Model
In earlier versions of SQL Server, large strings over 8,000 bytes had to be stored in the text, ntext, and image data types which had a different programming model than the data types for shorter strings (char, nchar, varchar, nvarchar, binary, and varbinary). The Database Engine now supports a MAX length specification for the shorter data types. When MAX is specified, the data types can store the same size strings as text, ntext, and image values up to 2 gigabytes (GB), but are processed in the same way as when they store shorter strings.
For more information, see Using Large-Value Data Types.
Transact-SQL Enhancements: Recursive Queries
A Common Table Expressions (CTE) is a results set that is stored temporarily during the execution of a SELECT, INSERT, UPDATE, or DELETE statement. Using a CTE allows the use of recursive queries and can simplify logic by replacing the use of temporary tables or views.
For more information, see Using Common Table Expressions.
Transact-SQL Enhancements: New APPLY Operator
The APPLY operator supports invoking a table-valued expression (a table-valued function or subquery) for each row returned by an outer table reference. The table-valued expression is evaluated for each row in the outer table reference.
For more information, see Using APPLY.
Transact-SQL Enhancements: New PIVOT and UNPIVOT Operators
The new PIVOT and UNPIVOT operators rotate result sets by which columns become rows and rows become columns. This is called rotating data or creating cross-tab reports. These operations were possible in earlier versions of SQL Server, but required complex logic that is now simplified using PIVOT and UNPIVOT.
For more information, see Using PIVOT and UNPIVOT.
Transact-SQL Enhancements: Query Notifications
Query notifications allow an application to cache a result set and request the Database Engine to notify it if any of the underlying data is modified by another application. Applications using query notifications do not have to periodically verify the state of the data in the database. They only have to validate the data if the Database Engine notifies them of a change.
For more information, see Using Query Notifications.
Transact-SQL Enhancements: Bulk Operations on OPENROWSET
OPENROWSET now supports a BULK operation for bulk copying data from data files. OPENROWSET bulk operations are controlled using new table hints and bulk options, such as BULK_BATCHSIZE and FORMATFILE. The new bulk_column_alias clause supports assigning column names to bulk operation data.
For more information, see OPENROWSET (Transact-SQL), Table Hint (Transact-SQL).
Transact-SQL Enhancements: TOP Enhancements
The TOP operator has been enhanced to take any numeric expression (such as a variable name) instead of only an integer number to specify the number of rows returned by the operator. TOP can also now be specified in INSERT, UPDATE, and DELETE statements.
For more information, see TOP (Transact-SQL).
Transact-SQL Enhancements: Distributed Query
The EXECUTE statement now supports an AT LinkedServer clause that specifies executing a stored procedure on a linked server. The CONTAINS full-text predicate supports four-part names to execute queries against linked servers. Distributed queries also support the new large object data types nvarchar(max), varchar(max), and varbinary(max). New SQL Trace events have been added to help analyze the interaction of distributed queries and OLE DB providers.
For more information, see EXECUTE (Transact-SQL), CONTAINS (Transact-SQL), Data Type Mapping with Distributed Queries, and OLEDB Event Category.
Transact-SQL Enhancements: TABLESAMPLE
The new TABLESAMPLE clause limits the number of rows processed by a query. Unlike TOP, which returns only the first rows from a result set, TABLESAMPLE returns rows selected from throughout the set of rows processed by the query.
For more information, see Limiting Result Sets by Using TABLESAMPLE.
Transact-SQL Enhancements: New CASCADE Integrity Constraints
The REFERENCES clause now supports the SET NULL and SET DEFAULT cascading referential integrity actions. SET NULL specifies that cascading referential integrity actions set foreign keys to NULL. SET DEFAULT specifies that foreign keys are set to the default value defined for the column.
For more information, see Cascading Referential Integrity Constraints.
Transact-SQL Enhancements: Overflow Data Can Exceed Page Size
Rows can exceed the limit of 8,060 bytes if they contain variable-length columns using the nvarchar, varchar, varbinary, and sql_variant data types. If the combined widths of these variable-length columns cause the row to exceed the page size limit, the Database Engine may move data from variable-length columns to separate pages.
For more information, see Row-Overflow Data Exceeding 8 KB.
Database Administration Programming: New SQL Server Management Objects (SMO) API
The SMO object model extends and supersedes the Distributed Management Objects (DMO) object model. The SMO object model provides classes for configuring and managing instances of the Database Engine. The Replication Management Objects (RMO) object model complements the SMO object model, providing classes for configuring and managing replication.
For more information, see SQL Server Management Objects (SMO).
Database Administration Programming: New SQL Server WMI Providers
The WMI Provider for Computer Management is an instance provider. The purpose of this layer is to provide a unified way for interfacing with the API calls that manage the registry operations requested by the Server and Client network utility functionality and that provide enhanced control and manipulation over the selected SQL services of the Computer Manager snap-in component.
For more information, see WMI Provider for Configuration Management.
The WMI Provider for Server Events enables you to use the Windows Management Instrumentation (WMI) to monitor events in SQL Server. The provider works by turning SQL Server into a managed WMI object. Any event that can generate an event notification in SQL Server can be leveraged by the WMI using this provider. Additionally, as a management application that interacts with the WMI, SQL Server Agent can respond to these events, increasing the scope of events covered by SQL Server Agent over previous releases.
For more information, see WMI Provider for Server Events.