Here's the recording link for Tobias' advanced T-SQL geekSpeak.

MSDN geekSpeak: Advanced T-SQL Techniques with Tobias Thernstrom

Also, here are some post-show resources:

Cross Apply http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Common Table Expression (CTE) http://www.4guysfromrolla.com/webtech/071906-1.shtml

Try/Catch http://msdn2.microsoft.com/en-us/library/ms179296.aspx

The book Tobias authored http://www.microsoft.com/MSPress/books/authors/auth10624.aspx

Here's the T-SQL code samples

--APPLY

SELECT TOP(10)
    c.CustomerID
    ,c.AccountNumber
    /*
    ,(SELECT TOP(1) soh.SalesOrderID FROM Sales.SalesOrderHeader soh
        WHERE soh.CUstomerID = c.CustomerID
        ORDER BY soh.OrderDate DESC)
    ,(SELECT TOP(1) soh.DueDate FROM Sales.SalesOrderHeader soh
        WHERE soh.CUstomerID = c.CustomerID
        ORDER BY soh.OrderDate DESC)
    */
    ,a.*
FROM Sales.Customer AS c
CROSS APPLY (SELECT TOP(3) soh.SalesOrderID, soh.DueDate FROM Sales.SalesOrderHeader soh
            WHERE soh.CUstomerID = c.CustomerID
            ORDER BY soh.OrderDate DESC) AS a

-- OUTPUT:
BEGIN TRAN;
    DECLARE @tmp TABLE (ProductID INT PRIMARY KEY);
    UPDATE Production.Product SET
        ListPrice = ListPrice * 1.1
    OUTPUT inserted.ProductID INTO @tmp
    WHERE Color = 'Red';

    SELECT sod.SalesOrderID FROM Sales.SalesOrderDetail AS sod
    INNER JOIN @tmp AS p ON p.ProductID = sod.ProductID;
COMMIT;

-- SNAPSHOT
-- READCOMMITTED SNAPSHOT

ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

DROP TABLE TestProducts;

SELECT TOP(10) * INTO TestProducts FROM Production.Product;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT Name, ListPrice FROM TestProducts
    WHERE Color = 'Black';

    SELECT COUNT(*) FROM TestProducts
    SELECT COUNT(*) FROM TestProducts

    SELECT Name, ListPrice FROM TestProducts WITH(READCOMMITTED)
    WHERE Color = 'Black';

    UPDATE TestProducts SET
        ListPrice = 10
    WHERE Color = 'Black';

--CTE
WITH a AS (
    SELECT p.ProductID, p.ListPrice, p.Color
        ,ROW_NUMBER() OVER (ORDER BY p.ListPrice) AS RowNo
    FROM Production.Product AS p
    WHERE p.Color = 'Red'
), b AS (
    SELECT COUNT(*) AS TotRowCount FROM a
), c AS (
    SELECT * FROM a
    WHERE a.RowNo BETWEEN 11 AND 20
), d AS (
    SELECT COUNT(*) AS PageRowCount FROM c
)
SELECT * FROM c
CROSS JOIN b
CROSS JOIN d
ORDER BY c.RowNo;

--ROW_NUMBER
SELECT * FROM (
    SELECT
        c.CustomerID
        ,c.AccountNumber
        ,soh.SalesOrderID
        ,soh.DueDate
        ,ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY soh.OrderDate DESC) AS RowNo
    FROM Sales.Customer AS c
    INNER JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID
    ) AS a
WHERE RowNo BETWEEN 1 AND 3

WITH a AS (
    SELECT
        c.CustomerID
        ,c.AccountNumber
        ,soh.SalesOrderID
        ,soh.DueDate
        ,ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY soh.OrderDate DESC) AS RowNo
    FROM Sales.Customer AS c
    INNER JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID
    )
SELECT * FROM a
WHERE RowNo BETWEEN 1 AND 3

--TRY/CATCH
CREATE TABLE Test (ID INT PRIMARY KEY);

BEGIN TRY
    BEGIN TRAN;
        INSERT Test (ID) VALUES (1);
        INSERT Test (ID) VALUES (1);
        INSERT Test (ID) VALUES (2);
    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE() -- ERROR_
    --COMMIT TRAN;
END CATCH

SELECT * FROM Test ;

DELETE Test;