String Functions
Exact Numeric
Bit decimal
Tiny int money
Small int numeric
big int
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 xm
Type Conversion
CAST (expression AS datatype)
CONVERT (datatype, expression)
Ranking Functions
RANK NTILE
DENSE- RANK ROW- NUMBER
Grouping (Aggregate) Function
AVG MAX
BINARY CHECKSUM MIN
CHECKSUM SUM
CHECKSUM_AVG STDEV
COUNT STDEVP
COUNT- BIG VAR
GROUPING VARP
Table Functions
ALTER DROP
CREATE 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,yyy
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 Functions
ABS LOG10
ACOS PI
ASIN POWER
ATAN RADIANS
ATN2 RAND
CEILING ROUND
COS SIGN
COT SIN
DEGREES SQUARE
EXP SQRT
FLOOR TAN
LOG
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
Create a Stored Procedure
CREATE PROCEDURE name
@variable AS data type = value
AS
–Comments
SELECT* FROM table
GO
Create a Trigger
CREATE PROCEDURE name
@variable AS data type = value
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 data type(tength))
RETURNS
datatype(length)
AS
BEGIN
DECLARE@return data type(length)
SELECT return CASE @variable
WHEN ‘a’ THEN ‘returns’
WHEN ‘b’ THEN ‘return b’
ELSE ‘return c’
RETURN @return
END