New features in SQL 2005 in brief:
- SQL Server 2005 as .NET runtime host: enhancing security, reliability, and performance
- Writing procedures, functions, and triggers in .NET languages
- Leveraging powerful new enhancements to T-SQL
- XML Support
- SQL Server 2005 as a Web Services platform
Enhancements in T-SQL
New Data Types:
- Varchar(max),Varbinary(max),Nvarchar(max) -Alternative to Text, Image
- 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
- An expression that produces a table that is referred to by name within the context of a single query
- 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