Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server mathematical functions


XLeratorDB/math

Use XLeratorDB/math for a wide variety of mathematical calculations. The feature-rich XLeratorDB function library lets you include calculations in any T-SQL statement including SELECT, INSERT, UPDATE, DELETE, CREATE VIEW as well as in CTEs, stored procedures, user-defined functions, and computed columns.

XLeratorDB/math is available in two packages - XLeratorDB/math which requires SQL Server 2005 or later, and XLeratorDB/math 2008 which requires SQL Server 2008 or later. 

Functions denoted with 'Not available for SQL2005' are only available in the XLeratorDB/math 2008 package and will not install or run  in SQL Server 2005. XLeratorDB 2008 packages contain functions which leverage SQL CLR features that are only available in SQL Server 2008 or later, namely 'multi-input aggregates' which provide a much easier calling syntax and typically run much faster than their scalar counterparts.

FUNCTION REFERENCE - MATH FUNCTIONS
ARITHMETIC/ALGEBRA
Arithmetic
COMBIN
 
Combinatorial
 
DISTANCE
 
Calculate the distance between two points on earth
 
EGCD
 
Euclidean Greatest Common Divisor
 
ELCM
 
Euclidean Least Common Multiple
 
EVEN
 
Nearest Even
 
FACT
 
Factorial
 
FACTDOUBLE
 
Double Factorial
 
GCD
 
Greatest Common Divisor
 
LCM
 
Least Common Multiple
 
MAX
 
Return the nth largest distinct value
Not available for SQL2005
MIN
 
Return the nth smallest distinct value
Not available for SQL2005
MROUND
 
Round to a multiple
 
MULTINOMIAL
 
Ratio of the factorial of a sum of values to the product of factorials  
 
MULTINOMIAL_q
 
Ratio of the factorial of a sum of values to the product of factorials  
 
ODD
 
Nearest Odd
 
PRODUCT
 
Product of Values (similar to SUM)
 
PRODUCT38
 
Product of 2 decimal(38,18) values with precision 38 and scale 18
 
QUOTIENT
 
Integer portion of a division
 
QUOTIENT38
 
Quotient of 2 decimal(38,18) values with precision 38 and scale 18
 
SERIESSUM
 
Sum of a Power Series  
 
SERIESSUM_q
 
Sum of a Power Series  
 
SUMSQ
 
Sum of the Squares
 
WAVG
 
Weighted average
Not available for SQL2005
 
Equalities
GREATEROF
 
Return the comparative larger of two items based on the datatype of first item
 
LESSEROF
 
Return the comparative smaller of two items based on the datatype of first item
 
 
Rounding
CEILING
 
Ceiling to a multiple
 
FLOOR
 
Floor to a multiple
 
INT
 
Integer
 
ROUNDDOWN
 
Round toward zero
 
ROUNDUP
 
Round away from zero
 
TRUNC
 
Truncate to Integer
 
 
Formatting
ROMAN
 
Convert to Roman Numerals
 
 
TRIGONOMETRIC
ACOSH
 
Inverse Hyperbolic Cosine
 
ASINH
 
Inverse Hyperbolic Sine
 
ATANH
 
Inverse Hyperbolic Tangent
 
COSH
 
Hyperbolic Cosine
 
CSC
 
Cosecant
 
SEC
 
Secant
 
SINH
 
Hyperbolic Sine
 
TANH
 
Hyperbolic Tangent
 
 
NUMERICAL INTEGRATION
QUAD
 
Evaluate an infinite integral using 15-point Gauss-Kronrod Quadrature
 
QUADDE
 
Evaluate an infinite integral using Double Exponential Quadrature for non-Periodic functions
 
QUADGK
 
Evaluate a finite integral using 21-point Gauss-Kronrod Quadrature
 
QUADOSC
 
Evaluate an infinite integral using Double Exponential Quadrature for Periodic functions
 
QUADTS
 
Evaluate a finite integral using Tanh-Sinh method
 
 
INTERPOLATION
BILINEARINTERP
 
2-dimensional interpolation  
 
INTERP
 
Linear interpolation
 
INTERP_q
 
Linear interpolation  
 
INTERP2
 
2-dimensional interpolation
Not available for SQL2005
LINEAR
 
Linear interpolation with extrapolation
 
MONOSPLINE
 
Monotonic spline interpolation
Not available for SQL2005
POLYCOEF
 
Polynomial coefficient
Not available for SQL2005
POLYFIT
 
Polynomial fitting  
 (table-valued function) 
POLYFIT_q
 
Polynomial fitting  
 (table-valued function) 
POLYINTERP
 
Polynomial interpolation
Not available for SQL2005
POLYRSQ
 
Polynomial goodness of fit
Not available for SQL2005
POLYVAL
 
Polynomial value
Not available for SQL2005
SPLINE
 
Cubic spline interpolation
 
SPLINE_q
 
Cubic spline interpolation  
 
 
MATRIX FUNCTIONS
Matrix Factorization
LU
 
Decompose a string representation of a matrix into the product of lower and upper triangular matrices
 
LUdecomp
 
Decompose a de-normalized matrix into lower and upper  
 
LUdecomp_q
 
Decompose a de-normalized matrix into lower and upper  
 
LUdecompN
 
Decompose a normalized matrix into lower and upper  
 
LUdecompN_q
 
Decompose a normalized matrix into lower and upper  
 
MSYMMEIG
new!
Calculate the D and V matrices representing the eigenvalues and eigenvectors of a real symmetric matrix 
 (table-valued function) 
QR
 
Decompose a string representation of a matrix into the product of an upper triangular matrix and an orthogonal matrix
 
QRdecomp
 
Decompose a de-normalized matrix into the product of an upper triangular matrix and an orthogonal matrix  
 
QRdecomp_q
 
Decompose a de-normalized matrix into the product of an upper triangular matrix and an orthogonal matrix  
 
SYMMEIG
new!
Calculate the D and V matrices representing the eigenvalues and eigenvectors of a real symmetric matrix 
 (table-valued function) 
 
Matrix Math
BKSUB
 
Solve for the equation A*x=b for an upper-triangular matrix
 
CORRM
Calculate the correlation matrix  
 (table-valued function) 
COVM
Calculate the sample covariance matrix  
 (table-valued function) 
CROSSPROD
Calculate A' * A or A' * B
 
FWDSUB
 
Solve for the equation A*x=b for a lower-triangular matrix
 
MATADD
 
Add a value to every element of a matrix or add 2 matrices together
 
MATINVERSE
 
Calculate the matrix inverse of a square (N x N) array
 
MATMULT
 
Calculate the product of two matrices or multiply a matrix by a constant
 
MATSUB
 
Subtract a value from every element of a matrix or subtract two matrices
 
MCORR
Calculate the correlation matrix
 
MCOV
Calculate the sample covariance matrix
 
MCROSS
Calculate A' * A or A' * B  
 (table-valued function) 
MDETERM
 
Matrix Determinant  
 
MDETERM_q
 
Matrix Determinant  
 
MDETERMN
 
Matrix Determinant (normalized input)  
 
MDETERMN_q
 
Matrix Determinant (normalized input)  
 
MINVERSE
 
Matrix Inverse  
 
MINVERSE_q
 
Matrix Inverse  
 
MINVERSEN
 
Matrix Inverse (normalized input)  
 
MINVERSEN_q
 
Matrix Inverse (normalized input)  
 
MMULT
 
Matrix Multiplication  
 
MMULT_q
 
Matrix Multiplication  
 
MMULTN
 
Matrix Multiplication (normalized input)  
 
MMULTN_q
 
Matrix Multiplication (normalized input)  
 
MRANK
new!
Calculate the rank of a matrix 
 
MTRACE
 
Calculate the trace of a matrix  
 
MTRACE_q
 
Calculate the trace of a matrix  
 
MTRACEN
 
Calculate the trace of a matrix (normalized input)  
 
MTRACEN_q
 
Calculate the trace of a matrix (normalized input)  
 
MTRIL
 
Return the lower triangular part of the string representation of a matrix
 
MTRIU
 
Return the upper triangular part of the string representation of a matrix
 
SPECRAD
new!
Calculate the spectral radius of a square matrix 
 
SVD
Economy-sized singular value decomposition
 
SVdecomp
Economy-sized singular value decomposition for de-normalized tables  
 (table-valued function) 
TCROSSPROD
Calculate A * A' or A * B'
 
TMCROSS
Calculate A * A' or A * B'  
 (table-valued function) 
TRANSPOSE
 
Return the transposed matrix
 
 
Matrix Randomization
MRAND
 
Generate a matrix of pseudo-random numbers
 
MRANDN
 
Generate a matrix of pseudo-random numbers from the standard normal distribution
 
MRORTHO
new!
Generate an m-by-m random orthogonal matrix 
 
 
Matrix Helper Functions
DIAG
 
Vector of the diagonal of the string representation of a matrix
 
EYE
 
Generate an m-by-n identity matrix
 
MAPPEND
new!
Append either rows or columns to a matrix 
 
MATRIX2STRING
 
Turn table data into a string representation of a matrix  
 
MATRIX2STRING_q
 
Turn table data into a string representation of a matrix  
 
MCOLS
 
Return the number of columns in the string representation of a matrix
 
MCOLUMN
 
Return a column from the string representation of a matrix
 
MINDEX
 
Return the intersection of a row and a column from the string representation of a matrix
 
MROW
 
Return a row from the string representation of a matrix
 
MROWS
 
Return the number of rows in the string representation of a matrix
 
MUPDATE
new!
Perform element-wise operations on a matrix or some portion of a matrix 
 
NMATRIX2STRING
 
Turn third-normal form table data into a string representation of a matrix
 
NMATRIX2STRING_q
 
Turn third-normal form table data into a string representation of a matrix  
 
ONES
 
Generate an m-by-n matrix of ones
 
ZERO
 
Generate an m-by-n matrix of zeroes
 
 
NUMBER/SERIES GENERATORS
Random Number Functions
RAND
 
Generate a uniform random float >=0 and <1
 
RANDBETWEEN
 
Generate a uniform random integer between two values
 
RANDNORM
 
Generate a random integer based on the normal distribution
 
 
Random Number Generators
RANDBETA
 
Generate a sequence of random numbers from the beta distribution
 (table-valued function)
RANDBINOM
 
Generate a sequence of random numbers from the binomial distribution
 (table-valued function)
RANDCAUCHY
 
Generate a sequence of random numbers from the Cauchy distribution
 (table-valued function)
RANDCHISQ
 
Generate a sequence of random numbers from the chi-squared distribution
 (table-valued function)
RANDEXP
 
Generate a sequence of random numbers from the exponential distribution
 (table-valued function)
RANDFDIST
 
Generate a sequence of random numbers from the F-distribution
 (table-valued function)
RANDGAMMA
 
Generate a sequence of random numbers from the gamma distribution
 (table-valued function)
RANDLAPLACE
 
Generate a sequence of random numbers from the LaPlace distribution
 (table-valued function)
RANDLOGISTIC
 
Generate a sequence of random numbers from the logistic distribution
 (table-valued function)
RANDNORMAL
 
Generate a sequence of random numbers from the normal distribution
 (table-valued function)
RANDPOISSON
 
Generate a sequence of random numbers from the Poisson distribution
 (table-valued function)
RANDSNORMAL
 
Generate a sequence of random numbers from the standard normal distribution
 (table-valued function)
RANDTDIST
 
Generate a sequence of random numbers from the Student's t distribution
 (table-valued function)
RANDWEIBULL
 
Generate a sequence of random numbers from the Weibull distribution
 (table-valued function)
 
Series Generators
SeriesInt
 
Generate a range of Integer values
 (table-valued function)
SeriesFloat
 
Generate a range of float values
 (table-valued function)
SeriesDate
 
Generate a range of date values
 (table-valued function)
 
MISC FUNCTIONS
RelativeError
 
Calculate the relative error between two values
 
 
XLDB_MATH_VERSION
 
Version Information
 

 

This function cannot reference data from SQL Server 2014 memory-optimized tables


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service