SQL Server 2005 Features

Index Enhancements

1. Rebuilding of Clustered index
            In SQL Server 2000, rebuilding a clustered index, was forcing all of the related non-clustered indexes also to be rebuild. In SQL Server 2005  rebuilding clustered index no longer force non-clustered indexes to be rebuilt.

Execute the following code in SQL Server 2000.

create table empl(eno int , ename varchar(20))

insert into empl values(1,'aa')

insert into empl values(2,'bb')

insert into empl values(3,'cc')

create clustered index empl_index on empl(eno)

create index empl_nc_index on empl(ename)

dbcc dbreindex('empl','empl_index')

Run the profiler trace , that shows the rebuild of all the related  non clustered indexes also.

Execute the above code in SQL Server 2005

Run the profiler trace. Only the clustered index would have got rebuilt. All the other non clustered indexes are not automatically rebuilt.

2. Adding Non-Key Columns to an Index
SQL Server 2005 also allows adding a Non-Key column to an index.

As previous Versions of SQL Server and almost all databases allow only key columns to be a part of index and so there is limited query support, which can take advantage of indexes. This new feature allows more queries to get executed by taking full advantage of indexes.

The heap or clustered index for a table (often called the “base table”) contains (or covers) all columns in the table.  Non-clustered indexes, on the other hand, contain (or cover) only a subset of the columns in the table. SQL Server 2005 allows the inclusion of non key columns into a non clustered index. When more columns are covered it improves queries performance

Note :- If a table contains a clustered index as well as a non clustered index , the non-clustered indexes will include the indexed column as well as the “cluster key”. 

Consider the following Example,

create table T_clu (a int, b int, c int, d int, e int)

create unique clustered index T_clu_a on T_clu (a)

create index T_clu_b on T_clu (b)

create index T_clu_ac on T_clu (a, c)

create index T_clu_d on T_clu (d) include (e)

Index	Covered Columns
T_Clu_a	A,b,c,d,e
T_Clu_b	A, b
T_Clu_ac	A,c
T_Clu_d	A,d,e

The non clustered index ‘T_Clu_d’ has more number of columns than other non clustered indexes. Included columns are more efficient than key columns; they save disk space and make searching and updating the index more efficient.

3. Enable and Disable indexes

Another new feature of SQL Server 2005 is ability to disable an index. Disabling an index stops that index to be being maintained and used by SQL Server engine. When an index is disabled, SQL Server 2005 database engine de-allocates the storage space used by an index but maintains the meta-data of that index. Alter Index statement is used to rebuild an index and only after that, disabled index can be enabled again.

Index can be disabled as follows

alter index emp_index on employee disable

To enable the index again, use the following command

Alter index emp_index on employee rebuild

      Rebuilding an index will enable the disabled index

4. Online index rebuild

      Previous versions of SQL Server didn't allow any access to an index while that index was rebuilt. Developers needed to wait until the rebuilt process completed and so database objects can get updated. SQL Server 2005 also allows online index operations, which enables applications to access the index and perform database operations like insert, update and delete on a table while the index is rebuilding.

SQL Enhancements

Using the OUTPUT Keyword

SQL Server triggers use the tables “inserted” and “deleted” to get information about the records that were manipulated by the current transaction. SQL Server 2005 introduces the OUTPUT keyword to provide this same functionality in a simple INSERT, UPDATE, and DELETE statement. You can use the OUTPUT keyword to return information about the results of a Transact-SQL statement into a table-valued variable.

Using the OUTPUT keyword

You can include an OUTPUT clause in any DML statement with the following exceptions:

INSERT statements targeting views

DML operations with remote tables or views

DML operations on local or distributed partitioned views

Examples of using the OUTPUT keyword

The following example shows how to use the OUTPUT keyword with an INSERT statement.

The example assumes a table called Stock.ProductList with an IDENTITY ProductID column, a ProductName column, and a Price column already exists.

DECLARE @InsertDetails TABLE

(

ProductID int,

InsertedBy sysname

)

INSERT INTO Stock.ProductList

OUTPUT inserted.ProductID, suser_name() INTO @InsertDetails

VALUES ('Racing Bike', 412.99)

SELECT * FROM @InsertDetails

The following example shows how to use the OUTPUT keyword with an UPDATE statement when updating the same sample table as in the previous example.

DECLARE @PriceChangeDetails TABLE

(

ProductID int,

OldPrice money,

NewPrice money,

UpdatedBy sysname

)

UPDATE Stock.ProductList

SET Price = 3.99

OUTPUT INSERTED.ProductID, DELETED.Price, INSERTED.Price,

  suser_name()

INTO @PriceChangeDetails

WHERE ProductID = 1

SELECT * FROM @PriceChangeDetails

The following example shows how to use the OUPUT keyword with a DELETE statement

DECLARE @DeleteDetails TABLE

(

ProductID int,

DeletedBy sysname

)

DELETE Stock.ProductList

OUTPUT DELETED.ProductID, suser_name() INTO @DeleteDetails

WHERE ProductID = 2

SELECT * FROM @DeleteDetails

Common Table Expressions (CTE)

Introduction

Common table expressions (CTEs) are defined in the SQL_99 standard, and let you create queries that work with recursive data. CTE-based recursive queries are generally more readable than those created using traditional Transact-SQL syntax.

Definition

A CTE is a named temporary result set based on a regular SELECT query. You then use the result set in any SELECT, INSERT, UPDATE, or DELETE query defined within the same scope as the CTE.

Advantages of CTEs

Using CTEs provides you with two main advantages:

• Queries with derived tables become simpler and therefore more readable.

Traditional Transact-SQL constructs used to work with derived tables usually require a separate definition for the derived data (such as a temporary table or table valued function). Using a CTE to define t he derived table makes it easier to see the definition of the derived table with the code that uses it.

• You can traverse recursive hierarchies.

CTEs reduce the amount of code required for a query that traverses recursive hierarchies (such as when rows in the same table can be linked with a self-join).

 

Example of a simple CTE

The following example shows the creation of a CTE named TopSales that displays the number of sales for each salesperson based on information provided by the SalesOrderHeader table.

WITH TopSales (SalesPersonID, NumSales) AS

( 

SELECT SalesPersonID, Count(*)

FROM Sales.SalesOrderHeader GROUP BY SalesPersonId 

)

SELECT * FROM TopSales

WHERE SalesPersonID IS NOT NULL

ORDER BY NumSales DESC

The query then restricts the TopSales CTE based on the SalesPersonID and sorts the rows based on the NumSales column.

Example of using a CTE and a join

The following example shows the same CTE named TopSales joined with the SalesPerson table to retrieve the salesperson's year-to-date sales figure and number of sales.

--assumes WITH TopSales from previous example

SELECT ts.SalesPersonID, sp.SalesYTD, ts.NumSales

FROM Sales.SalesPerson sp INNER JOIN TopSales ts

ON ts.SalesPersonID = sp.SalesPersonID

ORDER BY NumSales DESC

PIVOT and UNPIVOT Operators

Introduction

SQL Server 2005 introduces the PIVOT and UNPIVOT relational operators. These operators allow you to create powerful crosstab queries that form the basis of crosstab reports in a data warehouse scenario.

PIVOT definition

PIVOT lets you generate crosstab queries in which values are converted (or pivoted) to columns. A crosstab query calculates a sum, average, or other aggregation and then groups the result by row headings down the left side of a grid and by column headings across the top of a grid. The SQL Server 2005 PIVOT operator is similar to the Microsoft Access TRANSFORM statement.

 

UNPIVOT definition

UNPIVOT converts columns to values; the opposite of PIVOT. UNPIVOT has the same syntax as PIVOT except that you specify the UNPIVOT operator instead of PIVOT. The input table is narrowed from the columns in column_list into the single column in pivot_column.

 

Example of a simple PIVOT

In a sales environment, you might want to know the number of orders a customer places for individual products. Using the PIVOT operator

+achieves this aim.

The following example shows the structure of a simple Order table:

CREATE TABLE Sales.[Order]

(

Customer varchar(8), Product varchar(5), Quantity int

)

The table has the following values:

Customer	Product	Quantity
Mike	Bike	3
Mike	Chain	2
Mike	Bike	5
Lisa	Bike	3
Lisa	Chain	3
Lisa	Chain	4

The following example shows a PIVOT operation on the Order table:

SELECT * FROM Sales.[Order]

PIVOT (SUM(Quantity) FOR Product IN ([Bike],[Chain])) AS PVT

The expected output from this query is:

Customer	Bike	Chain
Lisa	3	7
Mike	8	2

Example of a simple UNPIVOT

The following example shows how to unpivot the PivotedOrder table to view the totals for each product:

SELECT Customer, Product, Quantity FROM Sales.PivotedOrder

UNPIVOT (Quantity FOR Product IN ([Bike],[Chain])) AS UnPVT

This produces the following results:

Customer	Product	Quantity
Lisa	Bike	3
Lisa	Chain	7
Mike	Bike	8
Mike	Chain	2

The APPLY Operator

Introduction

SQL Server 2005 introduces the APPLY relational operator, which allows you to apply a table-valued function to each row of a JOIN table. This technique is extremely flexible because you can use a user-defined function, allowing endless possibilities.

Definition ====

The APPLY operator invokes a function that returns a table for each row in a JOIN table. You can choose between the following APPLY operator types:

• CROSS APPLY. Only rows in the JOIN table are included if the row produces results in the table returned by the function.

• OUTER APPLY. All rows in the JOIN table are included regardless of whether the row produces results in the table returned by the function.

You can use the APPLY operator in any situation in which you use a FROM clause. The APPLY operator has the following syntax:

FROM left_table_source

{OUTER | CROSS} APPLY right_table_source

The main benefit of this operator comes when you use a table-valued function as right_table_source. This function normally accepts one or more columns from left_table_source as function parameters. The function uses these parameters to produce the table-valued result set.

Example of the APPLY operator

In this example, a report displaying customer order information relating to only the most recent three purchases is required. The MostRecentOrders function retrieves the required rows from the SalesOrderHeader table for a particular customer ID. The results are applied to the Store table that contains the customer information.

The following sample code shows the MostRecentOrders function:

CREATE FUNCTION Sales.MostRecentOrders(@CustID int)

RETURNS TABLE AS

RETURN

SELECT TOP (3) SalesOrderID, OrderDate

FROM Sales.SalesOrderHeader

WHERE CustomerID = @CustID

ORDER BY OrderDate DESC

In the following example, only the names of customers who have placed orders will appear in the output because of the use of a CROSS APPLY operator.

SELECT Name Customer, MR.*

FROM Sales.Store

CROSS APPLY Sales.MostRecentOrders(CustomerID) AS MR

In the following sample code, the names of all customers will appear in the output because of the use of an OUTER APPLY operator, even if they have not placed any orders.

SELECT Name AS Customer, MR.* FROM Sales.Store

OUTER APPLY Sales.MostRecentOrders(CustomerID) AS MR

Structured Exception handling

      Structured exception handling is a common way to handle exceptions in many popular programming languages, such as Microsoft Visual Basic .NET and Microsoft Visual C# .NET. SQL Server 2005 includes enhancements to Transact-SQL that allow you to use structured exception handling in any transactional situation, such as a stored procedure. This enhancement makes your code more readable and more maintainable.

Definition

Use TRY:CATCH blocks to implement structured exception handling. The TRY block contains the transactional code that could potentially fail. The CATCH block contains the code that executes if an error occurs in the TRY block.

TRY:CATCH has the following syntax:

BEGIN TRY

{ sql_statement | statement_block }

END TRY

BEGIN CATCH TRAN_ABORT

{ sql_statement | statement_block }

END CATCH

The sql_statement or statement_block part of the syntax is any Transact-SQL statement or group of statements.

Eliminates the need for "IF @@error"

In previous versions of SQL Server, you would handle exceptions by checking the @@error global variable. You would often check this variable immediately after an INSERT, UPDATE, or DELETE statement took place and then perform corrective action if @@error did not equal zero. The new structured exception handling approach reduces the amount of code you must create because you do not need to keep checking @@error.

Requires XACT_ABORT ON

One requirement for structured exception handling in SQL Server 2005 is that you must enable the automatic rolling back of transactions with SET XACT_ABORT ON. This setting raises all errors within a TRY block to the level of transactional abort errors. The CATCH block then handles the errors.

Transaction doomed state requires manual rollback

When the transaction fails, it enters a state known as the doomed state. The transaction remains open, but it cannot be committed. You must manually roll back the transaction that failed in the CATCH block by using the ROLLBACK TRANSACTION statement. This should be one of the first statements in the CATCH block to release the transaction resources as soon as possible.

Using @@error

If you need to use @@error, you must retrieve the value in the first statement of the CATCH block. If you intend to log this information in to an error log table, store @@error as a local variable and perform the log after the ROLLBACK statement. If you log the error before the ROLLBACK statement, you will also roll back the insertion into the error log.

Using RAISERROR

If you need to create your own exception in a TRY block, use the RAISERROR:WITH TRAN_ABORT statement. The WITH TRAN_ABORT option raises the error to the level of a transactional abort error, thereby invoking the CATCH block.

Example of a TRY:CATCH

In this example, a stored procedure named AddData attempts to insert two values into a table called TestData. The first column of the TestData table is an integer primary key, and the second column is an integer data type. A TRY:CATCH block within the AddData stored procedure protects the TestData INSERT statement and logs any errors in the ErrorLog table as part of the CATCH block logic.

CREATE TABLE dbo.DataTable (ColA int PRIMARY KEY, ColB int)

CREATE TABLE dbo.ErrorLog (ColA int, ColB int, error int,

date datetime)

GO

CREATE PROCEDURE dbo.AddData @a int, @b int AS

SET XACT_ABORT ON

BEGIN TRY

BEGIN TRAN

INSERT INTO dbo.DataTable VALUES (@a, @b)

COMMIT TRAN

END TRY

BEGIN CATCH TRAN_ABORT

DECLARE @err int

SET @err = @@error --trap the error number

ROLLBACK TRAN

INSERT INTO dbo.ErrorLog VALUES (@a, @b, @err, GETDATE())

END CATCH

GO

EXEC dbo.AddData 1, 1

EXEC dbo.AddData 2, 2

EXEC dbo.AddData 1, 3 --violates the primary key