Cheatography
https://cheatography.com
A cheat sheet for Microsoft SQL Server.
SQL Server Data Types
Exact Numerics |
bit |
decimal |
tinyint |
money |
smallint |
numeric |
bigint |
Approximate Numerics |
float |
real |
Date and Time |
smalldatetime |
timestamp |
datetime |
Strings |
char |
text |
varchar |
Unicode Strings |
nchar |
ntext |
nvarchar |
Binary Strings |
binary |
image |
varbinary |
Miscellaneous |
cursor |
table |
sql_variant |
xml |
SQL Server Type Conversion
CAST (expression AS datatype) |
CONVERT (datatype, expression) |
SQL Server Table Functions
ALTER |
DROP |
CREATE |
TRUNCATE |
|
|
SQL Server Grouping (Aggregate) Functions
AVG |
MAX |
BINARY_CHECKSUM |
MIN |
CHECKSUM |
SUM |
CHECKSUM_AVG |
STDEV |
COUNT |
STDEVP |
COUNT_BIG |
VAR |
GROUPING |
VARP |
SQL Server Ranking Functions
RANK |
NTILE |
DENSE_RANK |
ROW_NUMBER |
SQL Server String Functions
ASCII |
REPLICATE |
CHAR |
REVERSE |
CHARINDEX |
RIGHT |
DIFFERENCE |
RTRIM |
LEFT |
SOUNDEX |
LEN |
SPACE |
LOWER |
STR |
LTRIM |
STUFF |
NCHAR |
SUBSTRING |
PATINDEX |
UNICODE |
REPLACE |
UPPER |
QUOTENAME |
|
|
SQL Server Date Functions
DATEADD (datepart, number, date) |
DATEDIFF (datepart, start, end) |
DATENAME (datepart, date) |
DATEPART (datepart, date) |
DAY (date) |
GETDATE() |
GETUTCDATE() |
MONTH (date) |
YEAR (date) |
SQL Server Dateparts
yy, yyyy |
Year |
qq, q |
Quarter |
mm, m |
Month |
dy, y |
Day of Year |
dd, d |
Day |
wk, ww |
Week |
hh |
Hour |
mi, n |
Minute |
ss, s |
Second |
ms |
Millisecond |
SQL Server Mathematical Functions
ABS |
LOG10 |
ACOS |
PI |
ASIN |
POWER |
ATAN |
RADIANS |
ATN2 |
RAND |
CEILING |
ROUND |
COS |
SIGN |
COT |
SIN |
DEGREES |
SQUARE |
EXP |
SQRT |
FLOOR |
TAN |
LOG |
|
Created By
https://aloneonahill.com
Metadata
Favourited By
and 34 more ...
Comments
Great cheatsheet, missing one thing. can you add the range for the numerics data types.
Theb it would be perfect.
I second mrfatmen, adding the type size would be nice.
I like it, but what about datetime2 ? :-)
How about syntax-basics (SELECT, DELETE, UPDATE, etc.), some different JOINs, CTEs, and and and?
SELECT AFC.CASE_NUMBER AS 'CASE NUMBER', COUNT(AFC.CASE_NUMBER) AS CTOTAL, AETC.EVENT_ENTRY_ID, AEE.EVENT_ENTRY_DESC AS 'EVENT DESCRIPTION', AEE.EVENT_ENTRY_TYP_ID AS 'EVENT TYPE',
AETC.EVENT_DT AS 'EVENT DATE'
, COUNT(AEE.EVENT_ENTRY_TYP_ID) AS EVENTS
FROM dbo.ACM_FACTS_CASE AS AFC INNER JOIN
dbo.ACM_EVENT_TO_CASE AS AETC ON AFC.CASE_ID = AETC.CASE_ID INNER JOIN
dbo.ACM_EVENT_ENTRY AS AEE ON AETC.EVENT_ENTRY_ID = AEE.EVENT_ENTRY_ID
WHERE (AEE.EVENT_ENTRY_DESC LIKE '%mandate%')
and AETC.EVENT_DT between '2016-07-01' and '2018-06-30'
HAVING AEE.EVENT_ENTRY_TYP_ID > 1
ORDER BY AFC.CASE_NUMBER, AEE.EVENT_ENTRY_TYP_ID
Msg 8121, Level 16, State 1, Line 10
Column 'dbo.ACM_EVENT_ENTRY.EVENT_ENTRY_TYP_ID'
is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Add a Comment
Related Cheat Sheets
More Cheat Sheets by DaveChild