Show Menu
Cheatography

Forgotten T-SQL Cheat Sheet Cheat Sheet by

Inspired by MidnightDBA here's a reference sheet includes the Logical Processing Order of SELECT, shorthand for recursive CTEs and MERGE, the famous list-of-details XML trick, and more.

Logical Processing Order of SELECT

1. FROM table
2. ON join condition
3. JOIN table
4. WHERE clauses
5. GROUP BY columns
6. WITH CUBE / WITH ROLLUP
7. HAVING condition
8. SELECT columns
9. DISTINCT
10.ORDER BY columns
11.TOP % or number
The steps above show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps.

CTEs

; WITH cteName ( columnList )
AS ( SELECT statement )
SELECT columns
FROM cteName
INNER JOIN table ON condition
Below, is a list of those statements and/or clauses that cannot be used in ANY CTE.
COMPUTE or COMPUTE BY
ORDER BY (except when a TOP clause is specified)
INTO
OPTION clause with query hints
FOR XML
FOR BROWSE

Recusrsive CTEs

; WITH cteName ( columnList )
AS ( -- Anchor statement:
 ­ ­ ­ ­ ­ ­ ­ ­S­ELECT columns FROM table…
 ­ ­ ­ ­ ­ ­ ­ ­UNION ALL
 ­ ­ ­ ­ ­ ­ ­ -- Recursion statement:
 ­ ­ ­ ­ ­ ­ ­ ­S­ELECT columns FROM table…
 ­ ­ ­ ­ ­ ­ ­ ­INNER JOIN cteName ON
        )
SELECT columns
FROM cteName
Here are the statements and/or clauses that cannot be used in a recursive CTE:
SELECT DISTINCT
GROUP BY
HAVING
Scalar aggreg­ation (meaning you can't use min or max)
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)

OVER and PARTITION BY

/* Aggregate functions include COUNT, MIN,
MAX, AVG, ROW_CO­UNT(), etc. */

SELECT
 ­ ­ ­ ­agg­_fu­nc(­col1) OVER(),
    agg_func(col1)
 ­ ­ ­ ­ ­ ­ ­ ­OVE­R(P­ART­ITION BY col2),
columns
FROM table…
OVER allows you to get aggregate inform­ation without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it.
Using PARTITION BY the result set is broken into into partit­ions.
 

XML Trick: List of Details

/* Table2 holds detail rows for Table1; e.g., order details to order headers. */
SELECT columns,
 ­ ­ ­  colname = STUFF(
 ­ ­ ­ ( SELECT ','
 ­ ­ ­ ­ ­ ­ ­ + Name
 ­ ­ ­ ­ ­ ­ ­ ­F­ROM Table2
 ­ ­ ­ ­ ­ ­ ­ ­W­HERE Table1.ID = Table2.ID
 ­ ­ ­ ­ ­ ­ ­ ­ORDER BY Name
 ­ ­ ­ ­ ­ ­ ­ ­FOR XML PATH('')
 ­ ­ ­ ), 1, 1, '')
FROM    Table2

EXCEPT­/IN­TERSECT

SELECT col1, col2 FROM Table1
EXCEPT
SELECT col3, col4 FROM Table2

SELECT col1, col2 FROM Table1
INTERSECT
SELECT col3, col4 FROM Table2

MERGE

DECLARE @Changes
 ­ ­ ­ ­T­ABL­E(C­hange VARCHAR(20))
;  ­MERGE INTO DestTable
  USING
 ­ ( SELECT from sourceTable
 ­ ) AS Source ( columnList )
 ­ ­ON DestTa­ble.ID = Source.ID

 ­ ­WHEN MATCHED THEN
 ­ ­ ­ ­Action on destination
 ­ ­ ­ -- E.g., UPDATE SET col1 = 1
 ­ ­ ­ ­WHEN NOT MATCHED BY TARGET|SOURCE
 ­ ­ ­  Action on destination
 ­ ­ ­ -- E.g., INSERT (col1) VALUES(1)
 ­ ­O­UTPUT $action INTO @Changes
SELECT * FROM @Changes

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          More Cheat Sheets by renegrin

          Trello Cheat Sheet Keyboard Shortcuts
          Mobirise Keyboard Shortcuts