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

Leave a Reply

Your email address will not be published. Required fields are marked *