Listing 1

-- TOP Syntax
TOP (expression) [PERCENT]
    [ WITH TIES ]

Listing 2

-- TOP clause - SQL 2000 usage
SELECT TOP 7 *
FROM Employee
GO

Listing 3

-- TOP clause - dynamic SQL usage
DECLARE @Count INT
SET @Count = 7
DECLARE @SQL NVARCHAR(500)
SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR, @Count) + ' * FROM Employee'
EXEC  sp_executesql @SQL
GO

Listing 4

-- TOP clause - SQL 2005 usage
DECLARE @Count INT
SET @Count = 7
SELECT TOP (@Count) *
FROM Employee
GO

Listing 5

-- TRY-CATCH Syntax
BEGIN TRY
    { sql_statement | statement_block }
END TRY
BEGIN CATCH
    { sql_statement | statement_block }
END CATCH

Listing 6

-- TRY-CATCH Example
BEGIN TRY
    -- Generate a constraint violation error.
    DELETE
        FROM Production.Product
    WHERE ProductID = 980
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()    AS ErrorNumber,
        ERROR_SEVERITY()  AS ErrorSeverity,
        ERROR_STATE()     AS ErrorState,
        ERROR_MESSAGE()   AS ErrorMessage
END CATCH
GO

Listing 7

-- CTE Syntax
[ WITH  [ ,...n ] ]

<common_table_expression>::= expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

Listing 8

-- simple CTE
WITH Employee AS
(
    SELECT EmployeeID, MaritalStatus, Gender
    FROM HumanResources.Employee
)
SELECT *
FROM Employee
GO

Listing 9

-- CTE with column names
WITH Employee(MaritalStatus, Gender, Occurence) AS
(
    SELECT MaritalStatus, Gender, COUNT(*)
    FROM HumanResources.Employee
    GROUP BY MaritalStatus, Gender
)
SELECT *
FROM Employee
GO

Listing 10

-- CTE used multiple times
WITH Sales(SalesPersonID, TotalSales) AS
(
    SELECT SalesPersonID, SalesYTD + SalesLastYear
    FROM Sales.SalesPerson
)
SELECT 
    e.EmployeeID,
    es.TotalSales,
    e.ManagerID,
    ms.TotalSales
FROM HumanResources.Employee e
    LEFT JOIN Sales es
        ON e.EmployeeID = es.SalesPersonID
    LEFT JOIN Sales ms
        ON e.ManagerID = ms.SalesPersonID
GO

Listing 11

-- Recursive CTE
WITH Employees AS
(
    SELECT EmployeeID, ManagerID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID
    FROM HumanResources.Employee e
        INNER JOIN Employees CTE
            ON e.ManagerID = CTE.EmployeeID
)
SELECT *
FROM Employees
GO