Miscellaneous T-SQL Enhancements

Before concluding this chapter, let's look at some other enhancements made to T-SQL in SQL Server 2005 release.

The ALTER INDEX Statement

The ALTER INDEX statement, which is new in SQL Server 2005, can be used to disable, rebuild, or reorganize the XML and relational indexes. This statement can also be used to set the index options, such as whether row locks or page locks are allowed when accessing the index, whether index statistics should be automatically recomputed, and so on.

Note

You cannot use ALTER INDEX to add or remove columns or change the column order in an index or to repartition or move an index to a different file group. For such operations, you still have to use the CREATE INDEX statement with the DROP_EXISTING clause.

Here is an example of using ALTER INDEX to disable and enable an index:

USE AdventureWorks; GO ALTER INDEX AK_SalesOrderHeader_rowguid ON Sales.SalesOrderHeader DISABLE; GO SELECT is_disabled, * FROM sys.indexes WHERE name = 'AK_SalesOrderHeader_rowguid'; GO ALTER INDEX AK_SalesOrderHeader_rowguid ON Sales.SalesOrderHeader REBUILD; GO SELECT is_disabled, * FROM sys.indexes WHERE name = 'AK_SalesOrderHeader_rowguid'; GO

Caution

If you disable a clustered index on a table, SQL Server might have to disable a clustered index's dependent indexes. Also, access to the underlying table will be denied until the index is either dropped or enabled using the REBUILD clause.

Statement-Level Recompilation

In SQL Server 2000, when schema changes were made to the objects referenced in a stored procedure, when SET options were changed, or when statistics were changed, SQL Server would recompile the entire stored procedure. SQL Server 2005 introduces a new optimization technique in which only the statement that caused the recompilation, instead of entire stored procedure, is compiled. This results in faster recompile times, fewer compile locks, less CPU and memory utilization, and overall better performance in a T-SQL module. In addition to this built-in enhancement, a new query hint, OPTIMIZE FOR, is provided that you can use to instruct SQL Server to use a particular value for a local variable when the query is compiled and optimized.

New Server Configuration Options

The security-related changes in SQL Server 2005 might break your existing T-SQL scripts. For instance, by default, the xp_cmdshell extended stored procedure and SQL Mail stored procedures are disabled in SQL Server 2005. An error is raised when xp_cmdshell or SQL Mail procedures are executed. You can use the new security tool, SQL Server Surface Area Configuration, or the sp_configure statement to enable or disable features such as the xp_cmdshell extended stored procedure, SQL Mail procedures, Web Assistant stored procedures, and OLE Automation extended stored procedures. To view these options using sp_configure, you have to first enable the show advanced options server configuration option.

Synonyms

SQL Server 2005 permits creating synonyms that serve as alternative names for another database object, referred to as the base object. The base object can exist on a local or remote server. Synonyms can also be used to create a layer of abstraction that protects the client application from changes made to the name or location of the base object.

Note

A synonym cannot be the base object for another synonym, and a synonym cannot reference a user-defined aggregate function.

You can create a synonym by using the CREATE SYNONYM DDL statement, and you can remove it by using the DROP SYNONYM statement, as illustrated in the following example:

USE AdventureWorks; GO IF OBJECT_ID('edh') IS NOT NULL    DROP SYNONYM edh; GO CREATE SYNONYM edh FOR HumanResources.EmployeeDepartmentHistory; GO SELECT * FROM edh; GO SELECT * FROM sys.synonyms; GO IF OBJECT_ID('edh') IS NOT NULL    DROP SYNONYM edh; GO

You can use the sys.synonyms catalog view to view the metadata, including the base table name, for the synonym. When you run the CREATE SYNONYM statement, it is not required for the base object to exist at that time, and no permissions on the base object are checked. When the synonym is accessed, the base object existence and permission check is done. If base object and synonym owners are the same, SQL Server just ensures that the user accessing the synonym has sufficient permissions on the synonym. If the synonym and base object owners are different, SQL Server ensures that the user accessing the synonym has sufficient permissions on both the synonym and the base object.

The EXCEPT and INTERSECT Operators

The EXCEPT and INTERSECT operators allow you to compare the results of two or more SELECT statements and return distinct values. The EXCEPT operator returns any distinct values from the query on the left side of the EXCEPT operator that are not returned by the query on the right side. INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

Result sets that are compared using EXCEPT or INTERSECT must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types.

Here is an example of the EXCEPT and INTERSECT operators:

USE [tempdb]; GO IF OBJECT_ID('dbo.t1') IS NOT NULL    DROP TABLE dbo.t1; GO IF OBJECT_ID('dbo.t2') IS NOT NULL    DROP TABLE dbo.t2; GO CREATE TABLE dbo.t1(col1 int, col2 int); GO CREATE TABLE dbo.t2(col1 int, col2 int); GO INSERT INTO dbo.t1 SELECT 1, 1; INSERT INTO dbo.t1 SELECT 2, 2; INSERT INTO dbo.t1 SELECT 3, 3; INSERT INTO dbo.t2 SELECT 1, 1; INSERT INTO dbo.t2 SELECT 2, 2; GO SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2; GO SELECT * FROM dbo.t1 INTERSECT SELECT * FROM dbo.t2; GO

The first SELECT statement in this script returns a row with col1 and col2 as 3 because this is the row that is present in dbo.t1 and not in the dbo.t2 table. The second SELECT statement returns the first two rows because they are present in both the tables.

The SET SHOWPLAN_XML and SET STATISTICS XML Statements

SQL Server 2005 provides two new SET statementsSET SHOWPLAN_XML and SET STATISTICS XMLthat can be used to obtain a query showplan and statistics as an XML document. These XML results can then be processed in many ways, and they also open up the opportunity for third-party application vendors to provide add-on tools to optimize and compare performance results. Here is an example of these two statements:

USE AdventureWorks; GO SET SHOWPLAN_XML ON; GO SELECT * FROM Sales.Store; GO SET SHOWPLAN_XML OFF; GO SET STATISTICS XML ON; SELECT * FROM Sales.Store; SET STATISTICS XML OFF; GO

If you run the queries, you can see how the showplan and query statistics are returned as XML values. You can run the preceding script in Management Studio and view the results in grid mode. The showplan XML appears as a hyperlink. You can click the showplan XML hyperlink to view the XML text in a new window. You can copy and paste the showplan XML text into a text file that has the extension .sqlplan. Then you can start Windows Explorer and double-click the .sqlplan file or select File, Open (Ctrl+O) from Management Studio and open the .sqlplan file that you just created. You should notice that Management Studio reads the XML showplan and shows the execution plan graphically.