Microsoft SQL Server Cheat Sheet – Reference Sheet

Microsoft’s SQL Server is a powerful database server that integrates well with other Microsoft technologies like ASP and .NET, and includes some of the best database management tools available. The following reference lists the various functions available in SQL Server, and demonstrates the creation of stored procedures, triggers and functions.

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)


About onlinejt

Blogger

,

One Response to Microsoft SQL Server Cheat Sheet – Reference Sheet

  1. Guest 01/28/2011 at 2:36 am #

    This is a quick reference function list … good job.

Leave a Reply

*