Show Menu

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.
t-sql

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

Download the Forgotten T-SQL Cheat Sheet Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/renegrin_forgotten-t-sql-cheat-sheet.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

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