SQL Server 2008 / 2008 R2 – CheatSheet

SQL Server 2008 / 200 R2 CheatSheet will provide you a new way to learning the database. It is a 20 page PDF document which will give you a bird eye view of SQL Server 2008 and 2008 R2. Please use the password: Harinam to open the file.

This document wills also having the best interview SQL Query.

Try it and leave your comments / Suggestions / Changes.

Please download the following PDF file.

SQLServer2008R2_CheatSheet_V1.0

Thanks,
Harinam

Common Table Expressions

• 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.
SELECT ts.SalesPersonID, sp.SalesYTD, ts.NumSales FROM Sales.SalesPerson sp INNER JOIN TopSales ts
ON ts.SalesPersonID = sp.SalesPersonID ORDER BY NumSales DESC