Tag Archives: Funciton

Microsoft SQL Server Quick Reference

String Functions
Exact Numerics
bigint
bit
decimal
numeric
money
smallint
tinyint

Approximate Numerics
float
real

Date and Time
datetime
smalldatetime
timestamp

Strings
char
text
varchar

Unicode Strings
nchar
ntext
nvarchar

Binary Strings
binary
image
varbinary

Misc
cursor
sql_variant
table
xml

Type Conversions
CAST(expression AS datatype)
CONVERT(datatype, expression)

Ranking Functions
DENSE_RANK
NTITLE
RANK
ROW_NUMBER

Grouping Aggregate Functions
AVG
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AVG
COUNT
COUNT_BIG
GROUPING
MAX
MIN
SUM
STDEV
STDEP
VAR
VARP

Table Functions
ALTER
CREATE
DROP
TRUNCATE

Date Functions
DATEADD(datepart, number, date)
DATEDIFF(datepart, start, end)
DATENAME(datepart, date)
DATEPART(datepart, date)
DAY(date)
GETDATE()
GETUTCDATE()
MONTH(date)
YEAR(date)

Dateparts
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

Mathematical Funcations
ABS
ACOS
ASIN
ATAN
ATN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQUARE
SQRT
TAN

String Functions
ASCII
CHAR
CHARINDEX
DIFFERENCE
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
REPLACE
QUOTENAME
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
UNICODE
UPPER

Create a Stored Procedure
CREATE PROCEDURE name
@variable AS datatype = value
AS
–Comments
SELECT * FROM table
GO

Create a Trigger
CREATE TRIGGER name
ON
table
FOR
DELETE, INSERT, UPDATE
AS
–Comments
SELECT * FROM table
GO

Create a View
CREATE VIEW name
AS
–Comments
SELECT * FROM table
GO

Create an Index
CREATE UNIQUE INDEX name
ON
table(columns)

Create a Function
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