New in SQL Server 2005 for Developers (Part 1)
May be I’m writing this very late, but will be useful for someone who is looking for features that are introduced in 2005. The content may not be properly formatted as it was prepared in a hurry.
Common Table Expression
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
- Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
- Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
- Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
- Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
Sample (Northwind database):
WITH TopCustomers (City, Cnt)
AS
(
SELECT City, count(*) FROM customers
GROUP BY City
HAVING count(*) > 1
)
SELECT * FROM
Customers C inner join TopCustomers on C.City = TopCustomers.City
TOP function
SQL2005 allows TOP within the sub query also:
— Simple TOP query
SELECT TOP(5) *
FROM Employees;
— Nested TOP query
SELECT *
FROM (SELECT TOP(5) *
FROM Employees
ORDER BY [EmployeeId])
AS E
Order by EmployeeId Desc
Pivot Table
Another feature in 2005 is the Pivot table:
SELECT ProductId, Sum(Quantity*UnitPrice)
FROM [Order Details]
GROUP BY ProductId;
The same query as a Pivot table:
WITH Sales(ProductId, SalesAmt)
AS
(SELECT ProductId, Quantity*UnitPrice
FROM [Order Details])
SELECT ‘Sales’ AS SalesByProduct, [23], [46], [69], [15], [3]
FROM
(SELECT ProductId, SalesAmt FROM Sales) AS SourceTable
PIVOT
(
SUM(SalesAmt)
FOR ProductId IN ([23], [46], [69], [15], [3])
) AS PivotTable;
Though this doesn’t look very flexible, there will be instances where this can be applicable.
How many times have you sent yourself an email or SMS of something you want to remember, like a book or a URL and then it gets lost in your inbox, never to be seen again. With ShifD you can easily send an SMS of a note, place or link that will appear on your computer.
Now that there are many different 