Microsoft SQL Server Cheat Sheet – Reference Sheet

Data Functions

Dateparts

DATEADD (datepart, number, date)

DATEDIFF (datepart, start, end)

DATENAME (datepart, date)

DATEPART (datepart, date)

DAY (date)

GETDATE()

GETUTCDATE()

MONTH (date)

YEAR (date)

Year – yy, yyyy

Quarter – qq, q

Month – mm, m

Day of Year – dy, y

Day – dd, d

Week – wk, ww

Hour – hh

Minute – mi, n

Second – ss, s

Millisecond – ms

Type Conversion

Ranking Functions

CAST (expression AS datatype)

CONVERT (datatype, expression)

RANK – NTILE

DENSE_RANK -ROW_NUMBER

Mathematical Functions

String Functions

ABS – ACOS – ASIN

ATAN – ATN2 – CEILING

COS – COT – DEGREES

EXP – FLOOR – LOG

LOG10 – PI – POWER

RADIANS – RAND – ROUND

SIGN – SIN – SQUARE

SQRT – TAN

ASCII – CHAR -CHARINDEX

DIFFERENCE – LEFT – LEN

LOWER – LTRIM -NCHAR

PATINDEX – REPLACE – QUOTENAME

REPLICATE – REVERSE -RIGHT

RTRIM – SOUNDEX – SPACE

STR – STUFF – SUBSTRING

UNICODE – UPPER

Grouping (Aggregate) Functions

Table Functions

AVG – BINARY_CHECKSUM

CHECKSUM – CHECKSUM_AVG

COUNT – COUNT_BIG

GROUPING – MAX

MIN – SUM – STDEV

STDEVP – VAR – VARP

ALTER

CREATE

DROP

TRUNCATE

Datatypes

Create a Function

Exact Numerics:

Bit – tinyint – smallint – bigint

Decimal – money – numeric

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

CREATE FUNCTION name

(@variable datatype(length))

RETURNS

datatype(length)

AS

BEGIN

DECLARE @return datatype(length)

SELECT @return = CASE @variable

WHEN ‘a’ THEN ‘return a’

WHEN ‘b’ THEN ‘return b’

ELSE ‘return c’

RETURN @return

END

Create a Stored Procedure

Create a Trigger

CREATE PROCEDURE name

@variable AS datatype = value

AS

— Comments

SELECT * FROM table

GO

CREATE TRIGGER name

ON

table

FOR

DELETE, INSERT, UPDATE

AS

— Comments

SELECT * FROM table

GO

Create a View

Create an Index

CREATE VIEW name

AS

— Comments

SELECT * FROM table

GO

CREATE UNIQUE INDEX name

ON

table (columns)