average of non-NULLs
x 2005-2008r2
AVG() of {1,1,2,2,2,2,3,3} = 2Aggregate Functions
- AVG( [ DISTINCT ] z )
- CHECKSUM_AGG( [ DISTINCT ] z )
- COUNT( { [ DISTINCT ] z | * } )
- COUNT_BIG( { [ DISTINCT ] z | * } )
- GROUPING( c )
- GROUPING_ID( c [,...c] )
- MAX( [ DISTINCT ] z )
- MIN( [ DISTINCT ] z )
- STDEV( [ DISTINCT ] z )
- STDEVP( [ DISTINCT ] z )
- SUM( [ DISTINCT ] z )
- VAR( [ DISTINCT ] z )
- VARP( [ DISTINCT ] z )
checksum of non-NULLs
n 2005-2008r2
count of non-NULLs
n 2005-2008r2
COUNT() of {1,1,2,2,2,2,3,3} = 8as COUNT but bigint return
n 2005-2008r2
COUNT_BIG() of {1,1,2,2,2,2,3,3} = 8if column is aggregated in result set (1/0)
b 2005-2008r2
level of grouping for columns
n 2008r2 only
maximum value
z 2005-2008r2
MAX() of {1,1,2,2,2,2,3,3} = 3minimum value
z 2005-2008r2
MIN() of {1,1,2,2,2,2,3,3} = 1standard deviation for non-NULLs
x 2005-2008r2
STDEV() of {1,1,2,2,2,2,3,3} = 0.76 (2dp)stdev for population of non-NULLs
x 2005-2008r2
STDEVP() of {1,1,2,2,2,2,3,3} = 0.71 (2dp)total of non-NULLs
x 2005-2008r2
SUM() of {1,1,2,2,2,2,3,3} = 16variance of non-NULLs
x 2005-2008r2
VAR() of {1,1,2,2,2,2,3,3} = 0.57 (2dp)variance for population of non-NULLs
x 2005-2008r2
VARP() of {1,1,2,2,2,2,3,3} = 0.5Date & Time Functions
- CURRENT_TIMESTAMP
- DATEADD( datepart, n, d )
- DATEDIFF( datepart, dStart, dEnd )
- DATENAME( datepart, d )
- DATEPART( datepart, d )
- DAY( d )
- GETDATE()
- GETUTCDATE()
- ISDATE( s )
- MONTH( d )
- SWITCHOFFSET( d, sTimezone )
- SYSDATETIME()
- SYSDATETIMEOFFSET()
- TODAYTIMEOFFSET( d, sTimezone )
- YEAR( d )
- SYSUTCDATETIME()
current date and time
d 2005-2008r2
adds n intervals of datepart to d
d 2005-2008r2
'2012-01-01' ← DATEADD(day,1,'2011-12-31')
'2012-01-03' ← DATEADD(month,-1,'2012-02-03')number of intervals of datepart between dStart and dEnd
n 2005-2008r2
1 ← DATEDIFF(day,'2012-01-01','2011-12-31')name of d in terms of datepart
s 2005-2008r2
'January' ← DATENAME(month, '2012-01-01')
'1' ← DATENAME(day, '2012-01-01')number of d in terms of datepart
n 2005-2008r2
2012 ← DATEPART(year,'2012-04-27')
2 ← DATEPART(quarter,'2012-04-27')day of the month, 1-31
n 2005-2008r2
9 ← DAY('2011-11-09')current date and time
d 2005-2008r2
current date and time (offset to UTC)
d 2005-2008r2
true if a valid date, time or datetime
b 2005-2008r2
1 ← ISDATE('2012-01-01')
0 ← ISDATE('2011-02-29')
1 ← ISDATE('2012-02-29')
0 ← ISDATE('2012-02')month number, 1-12
n 2005-2008r2
11 ← MONTH('2011-11-09')applies timezone offset to d
d 2005-2008r2
'2012-01-01 18:00:00.0000000 +02:00' ← SWITCHOFFSET('2012-01-01 16:00', '+02:00')current date and time (as datetime2)
d 2008-2008r2
current date and time (as offset)
d 2008-2008r2
translates to a datetimeoffset
d 2005-2008r2
'2012-01-01 16:00:00.0000000 +02:00' ← TODATETIMEOFFSET('2012-01-01 16:00', '+02:00')4-digit year
n 2005-2008r2
2011 ← YEAR('2011-11-09')current date and time (offset to UTC as a datetime2)
d 2008-2008r2
Mathematical Functions
- ABS( x )
- ACOS( x )
- ASIN( x )
- ATAN( x )
- ATN2( x, x )
- CEILING( x )
- COS( x )
- COT( x )
- DEGREES( xradians )
- EXP( x )
- FLOOR( x )
- LOG( x )
- LOG10( x )
- PI()
- POWER( x, xponent )
- RADIANS( xdegrees )
- RAND( [ nseed ] )
- ROUND( x, ndigits, [ nfunction ] )
- SIGN( x )
- SIN( x )
- SQRT( x )
- SQUARE( x )
- TAN( x )
absolute value
x 2005-2008r2
1.0 ← ABS(-1.0)
0 ← ABS(0)
1.5 ← ABS(1.5)arccosine, in radians
x 2005-2008r2
0.25 ← ACOS(SQRT(0.5))/PI()arcsine, in radians
x 2005-2008r2
0.25 ← ASIN(SQRT(0.5))/PI()arctangent, in radians
x 2005-2008r2
0.25 ← ATAN(1)/PI()4-quadrant arctangent, in radians
x 2005-2008r2
-0.75 ← ATN2(-1,-1)/PI()nearest integer greater (contrasts with FLOOR())
n 2005-2008r2
8 ← CEILING(7.9)
7 ← CEILING(7)
-7 ← CEILING(-7.9)cosine, in radians
x 2005-2008r2
0.5 ← COS(PI()/3)cotangent, in radians
x 2005-2008r2
1 ← COT(PI()/4)angle of radian, in degrees
x 2005-2008r2
45 ← DEGREES(PI()/4)exponential value (contrasts with LOG())
x 2005-2008r2
20 ← EXP(LOG(20))nearest integer less (contrasts with CEILING())
n 2005-2008r2
7 ← FLOOR(7.2)
7 ← FLOOR(7)
-8 ← FLOOR(-7.2)natural logarithm (contrasts with EXP())
x 2005-2008r2
20 ← LOG(EXP(20))base-10 logarithm (contrasts with POWER())
x 2005-2008r2
5 ← POWER(10,LOG10(5))constant value π
x 2005-2008r2
3.14159265358979 ← PI()x to the power of xponent
x 2005-2008r2
16 ← POWER(2,4)
32 ← POWER(2,5)
100 ← POWER(10,2)
5 ← POWER(10,LOG10(5))
0 ← POWER(10,-2)angle of degrees, in radians
x 2005-2008r2
0.25 ← RADIANS(45.0)/PI()pseudo-random between 0 to 1 exclusive
x 2005-2008r2
? ← RAND()rounding (truncating with nfunction of 1)
x 2005-2008r2
2.000 ← ROUND(1.895, 0)
1.990 ← ROUND(1.985,2)
1.980 ← ROUND(1.985,2,1)-1/0/1 signifying <0/=0/>0
n 2005-2008r2
1.0 ← SIGN(9.9)
0 ← SIGN(0)
-1.0 ← SIGN(-9.9)sine, in radians
x 2005-2008r2
0.5 ← SIN(PI()/6)square root (contrasts with SQUARE())
x 2005-2008r2
5 ← SQRT(25)
5 ← SQRT(SQUARE(5))square (contrasts with SQRT())
x 2005-2008r2
25 ← SQUARE(5)
25 ← SQUARE(SQRT(25))tangent, in radians
x 2005-2008r2
1 ← TAN(PI()/4)Misc & System Functions
- CAST( z AS datatype [ (length) ] )
- CHECKSUM( { * | expression [,...expression] } )
- CONVERT( datatype [ (length) ], expression [, style ] )
- COALESCE( z [,...z] )
- DATALENGTH( z )
- ISNULL( z1, z2 )
- ISNUMERIC( z )
- NEWID()
- NULLIF( z1, z2 )
- PARSENAME( sObject, nObjectPiece )
type conversion
z 2005-2008r2
5 ← CAST(5 as int)
1 ← CAST(1 as bit)
1 ← CAST(4 as bit)
'123' ← CAST(123 as varchar(10))
'*' ← CAST(123 as varchar(2))returns the checksum value computed over a row of a table, or list of expressions
n 2005-2008r2
34400 ← CHECKSUM('abc')
34401 ← CHECKSUM('abd')
50 ← CHECKSUM(50)
4798 ← CHECKSUM(1,2,3,'a')type conversion
z 2005-2008r2
123 ← CONVERT(int, '123')first non-NULL (or NULL)
z 2005-2008r2
2 ← COALESCE(NULL,NULL,2,3)number of bytes to represent
n 2005-2008r2
4 ← DATALENGTH(123)
5 ← DATALENGTH(123.0)
3 ← DATALENGTH('abc')
6 ← DATALENGTH(N'abc')first non-NULL (or NULL)
z 2005-2008r2
'b' ← ISNULL(NULL,'b')numeric type validator
b 2005-2008r2
1 ← ISNUMERIC('321')
1 ← ISNUMERIC('123.45')
0 ← ISNUMERIC('123.45a')
0 ← ISNUMERIC('five')unique GUID
z 2005-2008r2
? ← NEWID()null if z1 and z2 are equal, z1 if not
z 2005-2008r2
'a' ← NULLIF('a','b')
NULL ← NULLIF('a','a')
'abc' ← NULLIF('abc',NULL)returns specified part of an object name. nObjectPiece: 1 = object, 2 = schema, 3 = database, 4 = server
s 2005-2008r2
'Object' ← PARSENAME('server.database.schema.Object',1)
'schema' ← PARSENAME('server.database.schema.Object',2)
'database' ← PARSENAME('server.database.schema.Object',3)
'server' ← PARSENAME('server.database.schema.Object',4)
NULL ← PARSENAME('server...Object',3)Rank & Window Functions
- DENSE_RANK() over-clause
- NTILE( n ) over-clause
- RANK() over-clause
- ROW_NUMBER() over-clause
- OVER( [ PARTITION BY ] z [,...z] ORDER BY z [,...z] )
- OVER( PARTITION BY z [,...z] )
dense ranks of row within partition
n 2005-2008r2
DENSE_RANK() of {a,a,b,b,b,b,c,c} = {1,1,2,2,2,2,3,3}groups of row within partition
n 2005-2008r2
NTILE(2) of {a,a,b,b,b,b,c,c} = {1,1,1,1,2,2,2,2}
NTILE(4) of {a,a,b,b,b,b,c,c} = {1,1,2,2,3,3,4,4}ranks of row within partition
n 2005-2008r2
RANK() of {a,a,b,b,b,b,c,c} = {1,1,3,3,3,3,7,7}row number within partition
n 2005-2008r2
ROW_NUMBER() of {a,a,b,b,b,b,c,c} = {1,2,3,4,5,6,7,8}rank window
2005-2008r2
ROW_NUMBER() OVER (ORDER BY column1, column2)aggregate window
2005-2008r2
SUM(column1) OVER (PARTITION BY column2)String Functions
- ASCII( s )
- CHAR( n )
- CHARINDEX( sFind, sFindIn [, nStart ] )
- DIFFERENCE( s, s )
- LEFT( s, n )
- LEN( s )
- LOWER( s )
- LTRIM( s )
- NCHAR( n )
- PATINDEX( { sPattern | '[%]sPattern[%]' }, s )
- QUOTENAME( s [, sQuote ] )
- REPLACE( s, sOld, sNew )
- REPLICATE( s, n )
- REVERSE( s )
- RIGHT( s, n )
- RTRIM( s )
- SOUNDEX( s )
- SPACE( n )
- STR( x [, nLength [, nDecimal ] ] )
- STUFF( s, nStart, nLength, sTo )
- SUBSTRING( s, nStart, nLength )
- UNICODE( s )
- UPPER( s )
ASCII value of char (contrasts with CHAR())
n 2005-2008r2
32 ← ASCII(' ')
68 ← ASCII('D')
100 ← ASCII('d')
100 ← ASCII('dIgnored')ASCII char from value (contrasts with ASCII())
s 2005-2008r2
'1' ← CHAR(49)
'N' ← CHAR(78)
'e' ← CHAR(101)
NULL ← CHAR(-5)first position of sFind within sFindIn
n 2005-2008r2
1 ← CHARINDEX('Find','FindMe')
0 ← CHARINDEX('Find','Not in here')
8 ← CHARINDEX('Find','FindMe-FindMe',2)difference between the SOUNDEX of two strings, 0 for no similarity through 4 for strong similarity.
n 2005-2008r2
4 ← DIFFERENCE('Green','Greene')
3 ← DIFFERENCE('Green','Greenish')
0 ← DIFFERENCE('Green','Red')left portion (contrasts with RIGHT())
s 2005-2008r2
'abc' ← LEFT('abcd',3)
'' ← LEFT('abcd',0)
'abcd' ← LEFT('abcd',10)number of chars
n 2005-2008r2
3 ← LEN('abc')
0 ← LEN('')all lower case (contrasts with UPPER())
s 2005-2008r2
'abcd' ← LOWER('aBcD')remove leading spaces (contrasts with RTRIM())
s 2005-2008r2
'lefty' ← LTRIM(' lefty')unicode char from value (contrasts with UNICODE())
s 2005-2008r2
'Ø' ← NCHAR(248)first position of pattern (with optional '%' wildcards)
n 2005-2008r2
2 ← PATINDEX('%in%','Find in here')
5 ← PATINDEX('% in %','Find in here')
0 ← PATINDEX('in%','Find in here')
10 ← PATINDEX('%ere','Find in here')
0 ← PATINDEX('%foo%','Not in here')delimits a string to make a valid SQL identifier
s 2005-2008r2
'[abcdef]' ← QUOTENAME('abcdef')
'[abc[]]def]' ← QUOTENAME('abc[]def')
'"abcdef"' ← QUOTENAME('abcdef','"')
'"abc[]def"' ← QUOTENAME('abc[]def','"')
'"abc""def"' ← QUOTENAME('abc"def','"')string find/replace
s 2005-2008r2
'AlphA' ← REPLACE('alpha','a','A')
'alfie' ← REPLACE('alpha','pha','fie')repeat s n times
s 2005-2008r2
'aaaaa' ← REPLICATE('a',5)
'abcabc' ← REPLICATE('abc',2)reverse char order
s 2005-2008r2
'cba' ← REVERSE('abc')
'abc' ← REVERSE(REVERSE('abc'))right portion (contrasts with LEFT())
s 2005-2008r2
'xyz' ← RIGHT('wxyz',3)
'' ← RIGHT('wxyz',0)
'wxyz' ← RIGHT('wxyz',10)remove trailing spaces (contrasts with LTRIM())
s 2005-2008r2
'righty' ← RTRIM('righty ')spelling homogenizer
s 2005-2008r2
'T600' ← SOUNDEX('Their')
'P600' ← SOUNDEX('Pair')
'P610' ← SOUNDEX('Parp')
SOUNDEX('Their') ← SOUNDEX('There')multiple spaces
s 2005-2008r2
' ' ← SPACE(3)string to number conversion
s 2005-2008r2
'123' ← STR(123.45)
'123' ← STR(123.45,3)
'**' ← STR(123.45,1)
'123.5' ← STR(123.45,6,1)
'123.450' ← STR(123.45,8,3)insert s into sTo at nStart for nLength chars
s 2005-2008r2
'aijklmnef' ← STUFF('abcdef',2,3,'ijklmn')portion of s from nStart for nLength chars
s 2005-2008r2
'abc' ← SUBSTRING('abcdef',1,3)
'bc' ← SUBSTRING('abcdef',2,2)
'ab' ← SUBSTRING('abcdef',0,3)
'cde' ← SUBSTRING('abcdef',3,3)
'ef' ← SUBSTRING('abcdef',5,3)unicode value from char (contrasts with NCHAR())
n 2005-2008r2
197 ← UNICODE('Å')
'Å' ← NCHAR(UNICODE('Å'))all upper case (contrasts with LOWER())
s 2005-2008r2
'ABCD' ← UPPER('aBcD')