Show Menu
Cheatography
  1. Home >
  2. Programming >

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:
 ­ ­ ­ ­ ­ ­ ­ SELECT columns FROM table…
 ­ ­ ­ ­ ­ ­ ­ UNION ALL
 ­ ­ ­ ­ ­ ­ ­ -- Recursion statement:
 ­ ­ ­ ­ ­ ­ ­ SELECT 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
 ­ ­ ­ ­ ­ ­ ­ FROM Table2
 ­ ­ ­ ­ ­ ­ ­ WHERE 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
 ­ ­ ­ TABLE(­Change VARCHA­R(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)
 ­ OUTPUT $action INTO @Changes
SELECT * FROM @Changes
 

Metadata

  • Languages:
  • Published: 29th January, 2016
  • Last Updated: 21st March, 2017
  • Rated: 4 stars based on 1 ratings

Favourited By

DaniLLo

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