SQL Server 2005 Enhancements

New features in SQL 2005 in brief:

1. SQL Server 2005 as .NET runtime host: enhancing security, reliability, and performance
2. Writing procedures, functions, and triggers in .NET languages
3. Leveraging powerful new enhancements to T-SQL
4. XML Support
5. SQL Server 2005 as a Web Services platform

Enhancements in T-SQL

New Data Types:

1. Varchar(max),Varbinary(max),Nvarchar(max) -Alternative to Text, Image
2. XML Data Type– Untyped and Strongly Typed

TOP ():

SQL 7.0 and 2000 provided TOP (n) with constant expression

SQL 2005 provides TOP ()
It can also be used with INSERT, UPDATE, and DELETE (in addition to SELECT)

Output Clause :
Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

Example :
USE AdventureWorks;

GO

DECLARE @MyTableVar table( ScrapReasonID smallint,

Name varchar(50),

ModifiedDate datetime);

INSERT Production.ScrapReason

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

INTO @MyTableVar

VALUES (N’Operator error’, GETDATE());

–Display the result set of the table variable.

SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

–Display the result set of the table.

SELECT ScrapReasonID, Name, ModifiedDate

FROM Production.ScrapReason;

GO

CTE & Recursive Queries

1. An expression that produces a table that is referred to by name within the context of a single query
2. Ability to traverse recursive hierarchies in a single query

Hierarchy in a table (MGRID-EMPID, Part-Subpart)

Find all employees reporting to a manager or

Find all parts required to assemble a product

Recursive, when references itself

Recursive form of CTE

UNION ALL

  Recursion stops when 2nd SELECT produces empty results   Example :   The following example shows the semantics of the recursive CTE structure by returning a hierarchical list of employees, starting with the highest ranking employee, in the Adventure Works Cycles company. The statement that executes the CTE limits the result set to employees in the Research and Development Group.   USE AdventureWorks;   GO   WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)   AS   (   — Anchor member definition   SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,   0 AS Level   FROM HumanResources.Employee AS e   INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh   ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL   WHERE ManagerID IS NULL   UNION ALL   — Recursive member definition   SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,   Level + 1   FROM HumanResources.Employee AS e   INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh   ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL   INNER JOIN DirectReports AS d   ON e.ManagerID = d.EmployeeID   )   — Statement that executes the CTE   SELECT ManagerID, EmployeeID, Title, Level   FROM DirectReports   INNER JOIN HumanResources.Department AS dp   ON DirectReports.DeptID = dp.DepartmentID   WHERE dp.GroupName = N’Research and Development’ OR Level = 0;   GO   Ranking Functions    ROW_NUMBER() – Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Example : SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS ‘Row Number’, SalesYTD, PostalCode FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;   RANK() – Returns the rank of each row within the partition of a result set. Example: USE AdventureWorks; GO SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS ‘RANK’ FROM Production.ProductInventory i INNER JOIN Production.Product p ON i.ProductID = p.ProductID ORDER BY p.Name; GO   DENSE_RANK() – Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.   Example – USE AdventureWorks;   GO   SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,   DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK   FROM Production.ProductInventory i   INNER JOIN Production.Product p   ON i.ProductID = p.ProductID   ORDER BY Name;   GO    NTILE() – Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.   Example : USE AdventureWorks;   GO   SELECT c.FirstName, c.LastName   ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS ‘Quartile’   ,s.SalesYTD, a.PostalCode   FROM Sales.SalesPerson s   INNER JOIN Person.Contact c   ON s.SalesPersonID = c.ContactID   INNER JOIN Person.Address a   ON a.AddressID = c.ContactID   WHERE TerritoryID IS NOT NULL   AND SalesYTD <> 0;   GO   PIVOT/UNPIVOT   PIVOT Transforms a set of rows to columns Useful for Reports/OLAP scenarios  Example : USE AdventureWorks ; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture;   DaysToManufacture AverageCost 0                              5.0885 1                              223.88 2                              359.1082 4                              949.4105 — Pivot table with one row and five columns SELECT ‘AverageCost’ AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4] FROM (SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) ) AS PivotTable;   Result : Cost_Sorted_By_Production_Days   0                 1                2                   3               4 AverageCost                                     5.0885        223.88       359.1082      NULL       949.4105 UNPIVOT   Reverse operation of PIVOT