This is a draft cheat sheet. It is a work in progress and is not finished yet.
Logical Processing Order
1 |
FROM |
2 |
ON |
3 |
JOIN |
4 |
WHERE |
5 |
GROUP BY |
6 |
WITH CUBE / WITH ROLLUP |
7 |
HAVING |
8 |
SELECT |
9 |
DISTINCT |
10 |
ORDER BY |
11 |
TOP |
Set Operators
A UNION B |
Distinct values from table A and B |
A UNION ALL B |
All values from table A and B |
A EXCEPT B |
All rows from table A except rows in B. |
A INTERSECT B |
Identical rows from table A and B |
SET STATISTICS ... [ON|OFF]
IO |
Disk activity |
TIME |
Parse, compile, & exec in milliseconds. |
|
|
CONVERT(varchar, getdate(), x)
101 |
mm/dd/yyyy |
102 |
yyyy.mm.dd |
103 |
dd/mm/yyyy |
104 |
dd.mm.yyyy |
105 |
dd-mm-yyyy |
106 |
dd mon yyyy |
107 |
mon dd, yyyy |
108 |
hh:mm:ss |
110 |
mm-dd-yyyy |
111 |
yyyy/mm/dd |
112 |
yyyymmdd |
114 |
hh:mm:ss:mmm |
120 |
yyyy-mm-dd hh:mm:ss |
126 |
yyyy-mm-ddThh:mm:ss.mmm |
Hints
OPTION (FORCE ORDER) |
Join order should be preserved in query optimizer |
|
|
Scalar functions
UCASE(str) |
Converts to upper case |
LCASE(str) |
Converts to lower case |
MID(str,start,end) |
Extract characters |
LEN(str) |
Length of str |
ROUND(value, dec.) |
Round a value |
NOW() |
Current systemtime |
FORMAT(value, format [, culture ]) |
RIGHT(str, num) |
Extract chars from right |
LEFT(str, num) |
Extract chars from left |
Aggregate Functions
AVG() |
Average value |
COUNT() |
Number of rows |
FIRST() |
First value |
LAST() |
Last value |
MAX() |
Max. value |
MIN() |
Min. value |
SUM() |
Sum of values |
|
