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 Available in XLeratorDB/math 2008 only 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
BASE 
 
Convert an integer to the text representation of that number for a given radix (base).
Available in XLeratorDB/math 2008 only
BASE2DECIMAL 
 
Convert the text representation of a number in a given radix (base) to an integer.
Available in XLeratorDB/math 2008 only
COMBIN
 
Calculate the number of combinations for a given number of items.
 
COMBINA 
 
Calculate the number of combinations with repitition of n things taken r at a time.
Available in XLeratorDB/math 2008 only
DISTANCE
 
Calculate the distance between 2 points on the globe, using the great circle formula.
 
EGCD
 
Calculate the greatest common divisor using Euclid's method.
 
ELCM
 
Calculate the least common multiple using Euclid's method.
 
EVEN
 
Calculate a number rounded up to the nearest even integer.
 
FACT
 
Calculate the factorial of a number.
 
FACTDOUBLE
 
Calculate the double factorial of a number.
 
GCD
 
Calculate a number rounded up to the nearest even integer.
 
LCM
 
Calculate the greatest common divisor of all the values, or only the DISTINCT values, in the expression.
 
MAX
 
Calculate the nth largest distinct value in a dataset.
Available in XLeratorDB/math 2008 only
MIN
 
Calculate the nth smallest distinct value in a dataset.
Available in XLeratorDB/math 2008 only
MROUND
 
Calculate a number rounded to the desired multiple.
 
MULTINOMIAL
 
Calculate the ratio of a sum of values to the product of the factorials of those values.
 
MULTINOMIAL_q
 
Calculate the ratio of a sum of values to the product of the factorials of those values.
 
ODD
 
Calculate a number rounded up to the nearest odd integer.
 
POWER
 
Calculate the value of the specified expression to the specified power.
 Available in XLeratorDB/math 2008 only
PRODUCT
 
Calculate the product of all the values, or only the DISTINCT values, in a dataset.
 
PRODUCT38
 
Calculate the product of 2 decimal(38,18) values as a decimal with precision 38 and scale 18.
 
QUOTIENT
 
Calculate integer portion of a division.
 
QUOTIENT38
 
Calculate the quotient of 2 decimal(38,18) values as a decimal with precision 38 and scale 18.
 
SERIESSUM
 
Calculate the sum of a power series.
 
SERIESSUM_q
 
Calculate the sum of a power series.
 
SUMSQ
 
Calculate the sum of the squares of all the values, or only the DISTINCT values, in the expression.
 
WAVG
 
Calculate the weighted average.
Available in XLeratorDB/math 2008 only
 
Equalities
GREATEROF
 
Calculate the greater of two values passed into the function.
 
LESSEROF
 
Calculate the lesser of two values passed into the function.
 
 
Rounding
CEILING
 
Calculate a number rounded up, away from zero, to the nearest multiple of significance.
 
FLOOR
 
Calculate a number rounded down, towards zero, to the nearest multiple of significance.
 
INT
 
Calculate a number rounded down, towards zero, to the nearest integer.
 
ROUNDDOWN
 
Calculate a number rounded down, towards zero.
 
ROUNDUP
 
Calculate a number rounded up, away from zero.
 
TRUNC
 
Calculate a number truncated to an integer by removing the fractional part of the number.
 
 
Formatting
ROMAN
 
Calculate an Arabic numeral converted to a Roman numeral, as text.
 
 
TRIGONOMETRIC
ACOSH
 
Calculate the inverse hyperbolic cosine of a number.
 
ACOT 
 
Calculate the arccotangent of a number.
Available in XLeratorDB/math 2008 only
ACOTH 
 
Calculate the hyperbolic arccotangent of a number.
Available in XLeratorDB/math 2008 only
ASINH
 
Calculate the inverse hyperbolic sine of a number.
 
ATANH
 
Calculate the inverse hyperbolic tangent of a number.
 
COSH
 
Calculate the hyperbolic cosine of a number.
 
COTH 
 
Calculate hyperbolic cotangent of a number.
Available in XLeratorDB/math 2008 only
CSC
 
Calculate the cosecant of the given angle.
 
CSCH 
 
Calculate the hyperbolic cosecant of the given angle.
Available in XLeratorDB/math 2008 only
SEC
 
Calculate the secant of the given angle.
 
SECH 
 
Calculate the hyperbolic secant of the given angle.
Available in XLeratorDB/math 2008 only
SINH
 
Calculate the hyperbolic sine of a number.
 
TANH
 
Calculate the hyperbolic tangent of a number.
 
 
NUMERICAL INTEGRATION
QUAD
 
Evaluate an infinite integral.
 
QUADDE
 
Evaluate an infinite integral.
 
QUADGK
 
Evaluate a finite integral.
 
QUADOSC
 
Evaluate an infinite integral.
 
QUADTS
 
Evaluate a finite integral.
 
 
INTERPOLATION
BILINEARINTERP
 
Interpolate on a regular 2-dimensional grid.
 
INTERP
 
Calculate the straight-line interpolated value of y given x in the array (x, y).
 
INTERP_q
 
Calculate the straight-line interpolated value of y given x in the array (x, y).
 
INTERP2
 
Interpolate on a 2-dimensional grid in 3rd normal form.
Available in XLeratorDB/math 2008 only
LINEAR
 
Calculate the straight-line interpolated value of y given x in the array (x, y).
 
MONOSPLINE
 
Calculate the interpolated value of y given x in the array (x, y).
Available in XLeratorDB/math 2008 only
POLYCOEF
 
Obtain a specific coefficient value from an approximating polynomial for a set of x- and y-values.
Available in XLeratorDB/math 2008 only
POLYFIT
 
Calculate the coefficients of a polynomial p(x) of degree that fits the x- and y-values supplied to the function.
 (table-valued function) 
POLYFIT_q
 
Calculate the coefficients of a polynomial p(x) of degree n ;that fits the x- and y-values supplied to the function.
 (table-valued function) 
POLYINTERP
 
Calculate the interpolated value of y given x in the array (x, y).
Available in XLeratorDB/math 2008 only
POLYRSQ
 
Calculate the goodness of fit for an approximating polynomial calculated from a series of x- and y-values for a specified number of degrees.
Available in XLeratorDB/math 2008 only
POLYVAL
 
Calculate a new y-value given a new x-value using the coefficients of a polynomial p(x) of degree that fits the x- and y-values supplied to the function.
Available in XLeratorDB/math 2008 only
SPLINE
 
Calculate the interpolated value of y given x in the array (x, y).
 
SPLINE_q
 
Calculate the interpolated value of y given x in the array (x, y).
 
 
MATRIX FUNCTIONS
Matrix Factorization
LU
 
Calculate the LU factorization of an N x N matrix using partial pivoting.
 
LUdecomp
 
Calculate the LU factorization of an N x N matrix using partial pivoting.
 
LUdecomp_q
 
Calculate the LU factorization of an N x N matrix using partial pivoting.
 
LUdecompN
 
Calculate the LU factorization of an N x N matrix using partial pivoting.
 
LUdecompN_q
 
Calculate the LU factorization of an N x N matrix using partial pivoting.
 
MSYMMEIG 
 
Return the D and V matrices representing the eigenvalues and eigenvectors of a real symmetric matrix.
Available in XLeratorDB/math 2008 only (table-valued function) 
QR
 
Decompose a string representation of an N x N matrix into the product of an upper triangular matrix and an orthogonal matrix Q, such that A=QR.
 
QRdecomp
 
Decompose a de-normalized N x N matrix A into the product of an upper triangular matrix R and an orthogonal matrix Q, such that A = QR.
 
QRdecomp_q
 
Decompose a de-normalized N x N matrix into the product of an upper triangular matrix R and an orthogonal matrix Q, such that A = QR.
 
SYMMEIG 
 
Return the D and V matrices representing the eigenvalues and eigenvectors of a real symmetric matrix.
Available in XLeratorDB/math 2008 only (table-valued function) 
 
Matrix Math
BKSUB
 
Return a solution to the equation A*x=b, when A is an upper-triangular matrix.
 
CORRM
Calculate a correlation matrix.
 (table-valued function) 
COVM
Calculate a sample covariance matrix.
 (table-valued function) 
CROSSPROD
Calculate the matrix cross-product of 2 matrices.
 
FWDSUB
 
Return a solution to the equation A*x=b, when A is a lower-triangular matrix.
 
MATADD
 
Add a value to every element of a matrix or to add 2 matrices of the same dimensions together.
 
MATINVERSE
 
Calculate the matrix inverse of a square (N x N) matrix.
 
MATMULT
 
Calculate the product of two matrixes or to multiply a matrix by a constant.
 
MATSUB
 
Subtract a value from every element of a matrix or to calculate the difference between 2 matrices of the same dimensions.
 
MCORR
Calculate a correlation matrix.
 
MCOV
Calculate a sample covariance matrix.
 
MCROSS
Calculate the matrix cross-product of 2 matrices.
 (table-valued function) 
MDETERM
 
Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.
 
MDETERM_q
 
Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.
 
MDETERMN
 
Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.
 
MDETERMN_q
 
Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.
 
MINVERSE
 
Calculate the matrix inverse of a square (N x N) matrix.
 
MINVERSE_q
 
Calculate the matrix inverse of a square (N x N) matrix.
 
MINVERSEN
 
Calculate the matrix inverse of a square (N x N) matrix.
 
MINVERSEN_q
 
Calculate the matrix inverse of a square (N x N) matrix.
 
MMULT
 
Calculate the matrix product of two arrays.
 
MMULT_q
 
Calculate the matrix product of two arrays.
 
MMULTN
 
Calculate the matrix product of two arrays in third-normal form.
 
MMULTN_q
 
Calculate the matrix product of two arrays in third-normal form.
 
MNORM 
 
Calculate the 1-norm, 2-norm, Infinity-norm, Frobenius norm, and maximum modulus of a matrix.
Available in XLeratorDB/math 2008 only
MRANK 
 
Calculate the rank of a matrix.
Available in XLeratorDB/math 2008 only
MTRACE
 
Calculate the trace of a de-normalized matrix.
 
MTRACE_q
 
Calculate the trace of a de-normalized matrix.
 
MTRACEN
 
Calculate the trace of a matrix in third-normal form.
 
MTRACEN_q
 
Calculate the trace of a matrix in third-normal form.
 
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 
 
Calculate the spectral radius of a square matrix.
Available in XLeratorDB/math 2008 only
SUMPRODUCT 
 
Calculate the sum of the elementwise multiplication of 1 or more matrices.
Available in XLeratorDB/math 2008 only
SUMX2MY2 
 
Calculate the sum of the differences of the squares in the corresponding elements in 2 matrices.
Available in XLeratorDB/math 2008 only
SUMX2PY2 
 
Calculate the sum of the sum of the squares in the corresponding elements in 2 matrices.
Available in XLeratorDB/math 2008 only
SUMXMY2 
 
Calculate the sum of the sqaure of the differences in the corresponding elements in 2 matrices.
Available in XLeratorDB/math 2008 only
SVD
Calculate the economy-sized singular value decomposition of an m-x-n matrix A.
 
SVdecomp
Calculate the economy-sized singular value decomposition of an m-x-n matrix A.
 (table-valued function) 
TCROSSPROD
Calculate the matrix cross-product of 2 matrices.
 
TMCROSS
Calculate the matrix cross-product of 2 matrices.
 (table-valued function) 
TRANSPOSE
 
Return the transposed matrix.
 
 
Matrix Randomization
MRAND
 
Generate an m-by-n matrix of pseudo-random numbers greater than or equal to zero and less than one.
 
MRANDN
 
Generate an m-by-n matrix of pseudo-random numbers from the standard normal distribution.
 
MRORTHO 
 
Generate an m-by-m random orthogonal matrix.
Available in XLeratorDB/math 2008 only
 
Matrix Helper Functions
DIAG
 
Return the vector of the diagonal of the string representation of a matrix.
 
EYE
 
Generate an m-by-n identity matrix.
 
MAPPEND 
 
Add columns or rows to an existing matrix variable.
Available in XLeratorDB/math 2008 only
MATRIX2STRING
 
Turn table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.
 
MATRIX2STRING_q
 
Turn table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.
 
MCOLS
 
Find 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
 
Find the number of rows in the string representation of a matrix.
 
MUPDATE 
 
Change the values in the string representation of a matrix or to perform element-wise operations on a matrix or some portion of a matrix.
Available in XLeratorDB/math 2008 only
NMATRIX2STRING
 
Turn third-normal form table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.
Available in XLeratorDB/math 2008 only
NMATRIX2STRING_q
 
Turn third-normal form table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.
 
ONES
 
Generate an m-by-n matrix of ones.
 
VANDERMONDE 
 
Return the Vandermonde matrix.
Available in XLeratorDB/math 2008 only
ZERO
 
Generate an m-by-n matrix of zeroes.
 
 
NUMBER/SERIES GENERATORS
Random Number Functions
RAND
 
Calculate a random number greater than or equal to zero and less than one.
 
RANDBETWEEN
 
Calculate a random integer number between the numbers you specify.
 
RANDNORM
 
Calculate a pseudo-random number based on a normal distribution for the given mean and standard deviation.
 
 
Random Number Generators
RANDBETA
 
Generate q sequence of random numbers from the beta distribution with two positive shape parameters alpha and beta.
 (table-valued function)
RANDBINOM
 
Generate a sequence of random integers from the binomial distribution for a given probability of success and a given number of trials.
 (table-valued function)
RANDCAUCHY
 
Generate a sequence of random numbers from a Cauchy distribution for a given location parameter and scale.
 (table-valued function)
RANDCHISQ
 
Generate a sequence of random numbers from a chi-squared distribution for a specified degrees of freedom.
 (table-valued function)
RANDEXP
 
Generate a sequence of random numbers from an exponential distribution with rate lambda.
 (table-valued function)
RANDFDIST
 
Generate a sequence of random numbers from an F-distribution with the degree of freedom parameters df1 and df2.
 (table-valued function)
RANDGAMMA
 
Generate a sequence of random numbers from a gamma distribution for the supplied shape and scale parameters.
 (table-valued function)
RANDLAPLACE
 
Generate a sequence of random numbers from a LaPlace distribution with supplied location and scales parameters.
 (table-valued function)
RANDLOGISTIC
 
Generate a sequence of random numbers from a logistic distribution of the supplied location and scale parameters.
 (table-valued function)
RANDNORMAL
 
Generate a sequence of random numbers from the normal distribution with mean μ and standard deviation σ.
 (table-valued function)
RANDPOISSON
 
Generate a sequence of random integers from the Poisson distribution for a given λ.
 (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 Student's t distribution with for the supplied degrees of freedom.
 (table-valued function)
RANDWEIBULL
 
Generate a sequence of random numbers from w Weibull distribution with parameters shape (λ) and scale (κ).
 (table-valued function)
 
Series Generators
SeriesInt
 
Generate a range of integer values.
 (table-valued function)
SeriesFloat
 
Generate a range of floating point values.
 (table-valued function)
SeriesDate
 
Generate a range of date values.
 (table-valued function)
 
Optimization
BFGS *!*
 
Find the minimum of a function using the Broyden-Fletcher-Goldfarb-Shanno (BFGS) method.
Available in XLeratorDB/math 2008 only
BRENT *!*
 
Find the root of a continuous function of one variable.
Available in XLeratorDB/math 2008 only
FDERIV *!*
 
Numerical function differentiation for orders n = 1 to 4 using finite difference approximations.
Available in XLeratorDB/math 2008 only
GRAD *!*
 
Numerically compute the gradient.
Available in XLeratorDB/math 2008 only
HESSIAN *!*
 
Numerically computer the Hessian matrix.
Available in XLeratorDB/math 2008 only
JACOBIAN *!*
 
Numerically compute the Jacobian matrix.
Available in XLeratorDB/math 2008 only
NEWTON *!*
 
Find the root of a univariate function.
Available in XLeratorDB/math 2008 only
NLMIN *!*
 
Nelder-Mead Minimization.
Available in XLeratorDB/math 2008 only (table-valued function) 
SECANT *!*
 
Find the root of single-variable continuous function.
Available in XLeratorDB/math 2008 only
 
MISC FUNCTIONS
 
XLDB_MATH_VERSION
 
Display version information for the XLeratorDB/math module.
 

 

 


*!*Added in Most Recent Release of package

*BETA This function is only available in the XLeratorDB SuitePLUS-2008 Trial package as a BETA preview


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

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service