New Transact-SQL Statements

Microsoft® SQL Server™ includes these new statements, or options to existing statements:

• These options have been added to ALTER DATABASE: ADD FILEGROUP, MODIFY FILE, MODIFY FILEGROUP, REMOVE FILE, and REMOVE FILEGROUP.
• ALTER DATABASE and CREATE DATABASE allow databases to be attached using the FOR ATTACH option.
• ALTER PROCEDURE and ALTER TRIGGER statements have been added.
• The DROP COLUMN clause in the ALTER TABLE statement provides the ability to specify a non (nonnull) default for a column. In addition, ALTER TABLE includes the ability to add a nonnull column with a default by using ALTER COLUMN. For more information, see ALTER TABLE.
• ROWGUIDCOL allows one uniqueidentifier column per table to be a rowguid. For more information, see ALTER TABLE and CREATE TABLE.
• SQL Server allows computed columns to be stored and retrieved. For more information, see ALTER TABLE, CREATE TABLE, and SELECT.
• The NOT FOR REPLICATION clause has been added to both ALTER TRIGGER and CREATE TRIGGER. This clause indicates that the trigger being created should not be executed when a replication login modifies the table involved in the given trigger.
• ALTER VIEW statement allows views to be altered without changing permissions.
• BACKUP statement has been added to allow full or partial database backup operations, and log backups.
• BULK INSERT copies a database table to or from a data file in a user-specified format.
• CAST conversion function is an ANSI/ISO synonym for the CONVERT conversion function.
• Specifying the WORK keyword is optional in the COMMIT WORK statement marking the end of a transaction.
• CONTAINS and FREETEXT predicates added to the WHERE clause for full-text querying.
• In earlier versions of SQL Server, a database had to be created before it could be restored. Because SQL Server version 7.0 now fully creates the destination database from a restore operation, it is recommended that you perform only the restore operation and not create the destination database to be restored. For more information, see FOR LOAD in CREATE DATABASE and RESTORE.
• CREATE STATISTICS creates a histogram and associated statistics groups for the specified column or set of columns.
• Bit columns created using CREATE TABLE can now allow NULLs. In addition, when a column with the IDENTITY property is defined with the NOT FOR REPLICATION clause, then:
  • A replication task can perform an INSERT with an explicit value specified for this column without having to execute SET IDENTITY_INSERT ON.
  • An INSERT statement by a replication connection does not change the current IDENTITY values.
• Multiple triggers for any trigger action (DELETE, INSERT, or UPDATE) can exist if either the WITH APPEND option is specified, or if the compatibility level setting equals 70. In addition, SQL Server adds direct trigger recursion. For more information, see CREATE TRIGGER.
• CREATE VIEW now allows the use of the system functions that have names that begin with two at signs (@@).
• DBCC CHECKFILEGROUP checks the allocation and structural integrity of all tables in the filegroup.
• DBCC SHRINKDATABASE shrinks the size of a database’s data files.
• DBCC SHRINKFILE shrinks the size of a data file in a database.
• Cursor variables created by using DECLARE @local_variable.
• Optimizer hints have been added to the DELETE, INSERT, UPDATE, and SELECT statements.
• DENY statement enhances security by creating an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.
• LOOP, HASH, MERGE, ROBUST PLAN, and FASTFIRSTROW optimizer hints (a READPAST optimizer hint exists only for SELECT) for DELETE, INSERT, SELECT, and UPDATE. For more information, see FROM.
• Specifying the WORK keyword is an option in the ROLLBACK WORK statement, which rolls back a user-specified transaction to the last savepoint inside a transaction or to the beginning of a transaction.
• RESTORE statement has been added to allow full or partial database backups or log backups to be restored. In addition, using either LOAD DATABASE or RESTORE DATABASE automatically creates the master database, if it does not already exist.
• REVOKE statement. In SQL Server 6.5, REVOKE either removed a grant or created a negative permission entry in sysprotects depending on the initial state of the user and group level permissions. In SQL Server 7.0, REVOKE always removes a grant or deny from sysprotects for the user, group, or role specified in the REVOKE statement. It does not create a negative entry. In SQL Server 7.0, negative entries are created using the DENY statement.
• READUNCOMMITTED, READCOMMITTED, REPEATABLEREAD, and table hints are added. For more information, see Table Hints in SELECT.
• TOP, PERCENT, and WITH TIES options have been added to the SELECT statement.
• SET @local_variable assigns a value to the specified local variable. In addition, SET can be used with cursor variables.
• When set in a batch, SET ANSI_NULLS now takes effect only from subsequent batches. If SET ANSI_NULLS is used inside a stored procedure, it does not take effect inside the stored procedure and a warning is issued.
• SET CONCAT_NULL_YIELDS_NULL, which, when ON, yields NULL if either of the operands in a concatenation operation is NULL.
• SET LOCK_TIMEOUT option specifies the number of seconds a statement waits for a lock to be released.
• SET QUERY_GOVERNOR_COST_LIMIT overrides the configured value of the query_governor_cost_limit option of sp_configure.
• SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements display showplan output. The SET SHOWPLAN_ALL statement shows all information including the parent identification number of the node being displayed. The SET SHOWPLAN_TEXT statement displays a small subset of the information that SET SHOWPLAN_ALL displays.
• Optimizer hints have been added to the UPDATE statement.