Microsoft® Transact-SQL provides new and extended functionality that allows greater control and flexibility for the Transact-SQL programmer.
The additions and enhancements are organized by feature. For items listed by component type, see these topics:
Microsoft SQL Server™ provides extended SQL statements and new system stored procedures for attaching and detaching databases.
| ALTER DATABASE FOR ATTACH | sp_attach_single_file_db |
| CREATE DATABASE FOR ATTACH | sp_detach_db |
| sp_attach_db |
SQL Server enhances cursor support.
| cursor | sp_describe_cursor_columns |
| sp_cursor_list | sp_describe_cursor_tables |
| sp_describe_cursor |
Deferred name resolution allows SQL Server stored procedures and triggers to reference the names of objects that do not yet exist. For more information, see Deferred Name Resolution and Compilation.
SQL Server creates a database by using a set of operating-system files. These new or extended SQL statements support the use of files and filegroups:
| ALTER DATABASE | CREATE INDEX |
| ALTER TABLE | CREATE TABLE |
| BACKUP | RESTORE |
| CREATE DATABASE |
Microsoft SQL Server supports distributed queries, which use a four-part database object naming scheme. For information about distributed queries, see Distributed Queries. These new functions and new or enhanced stored procedures are used with distributed queries:
SQL Server provides these new functions and stored procedures for full-text querying:
Information schema views provide table-independent access to the SQL Server metadata and allow applications to work properly even if significant changes have been made to the system tables. For more information, see Information Schema Views.
SQL Server job processing has changed from earlier versions of SQL Server. For more information, see Implementing Jobs. These stored procedures support the enhanced job processing functionality:
Optimizer hints have been added to the DELETE, INSERT, SELECT, and UPDATE statements.
SQL Server supports parallel execution of individual queries. Queries that must examine a large number of rows to produce a small result set such as joins, aggregations, or unions, can benefit from having portions of their execution plans run in parallel on multiple processors. For more information, see Parallel Query Processing.
SQL Server supports these new or extended property-related functions:
| COLUMNPROPERTY | INDEXPROPERTY |
| DATABASEPROPERTY | OBJECTPROPERTY |
| FILEGROUPPROPERTY | TYPEPROPERTY |
| FILEPROPERTY |
SQL Server replication has changed from earlier versions of SQL Server. These stored procedures and system tables support the enhanced replication functionality:
Showplan output options have changed considerably. SET SHOWPLAN_TEXT provides showplan information in text format. SET SHOWPLAN_ALL sends showplan information to another program for further display enhancement.
SQL Server provides enhanced security through these new or extended functions, statements, and stored procedures:
SQL Server includes SQL Server Profiler for monitoring SQL Server events. For more information, see Monitoring with SQL Server Profiler. These are the SQL Server Profiler related stored procedures:
SQL Server includes changes to COMMIT WORK and ROLLBACK WORK.