Problem
Is there a way to perform matrix operations or calculations directly within the database, eliminating the need for an external matrix calculator in SQL Server?
Solution
Matrix operations and calculations are fundamental in various computational tasks such as numerical analysis, data modeling, and machine learning.
By performing these operations directly within the database, this toolkit can:
- Improve Efficiency: Reduces data transfer between applications and databases, enabling faster computations.
- Simplifies Workflows: Consolidates data storage, manipulation, and computation in a single environment.
- Supports Advanced Analytics: Facilitates the implementation of mathematical models and algorithms directly within the database.
Matrix operations are used across numerous fields, including:
- Data Science and Machine Learning: For operations like matrix multiplications in predictive modeling and feature transformations.
- Engineering and Scientific Computing: For solving linear systems, simulations, and optimization problems.
- Finance: For portfolio analysis, risk modeling, and derivative pricing requiring matrix calculations.
- Business Intelligence: For handling multidimensional data for insights, aggregations, and trends.
- Operations Research: For linear programming and decision-making models.
Model and Rules to Use
To work with matrixes in SQL Server, I created a User-defined table type in the format row, column, and its value.
CREATE TYPE [dbo].[uttMtxIndexed] AS TABLE(
[lin] [smallint] NULL,
[col] [smallint] NULL,
[val] [float] NULL
)
GOSince I did not know the number n of rows to be entered in advance, I decided to standardize the data entry in a variable string in the format of values separated by space and rows by semicolon. To standardize and check if it attends this rule, I created the following User-defined-function:
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Cleanup String
-- =============================================
ALTER FUNCTION [dbo].[ufnMtxFlatCleanup]
(@String varchar(MAX))
RETURNS nvarchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
IF LEN(@String) = 0
RETURN '';
WHILE @String LIKE '% %' OR @String LIKE '%; %' OR @String LIKE '% ;%' BEGIN
-- Replace double spaces with a single space
IF @String LIKE '% %'
SET @String = REPLACE(@String,' ',' ');
-- Remove space after opening parenthesis
IF @String LIKE '%; %'
SET @String = REPLACE(@String,'; ',';');
-- Remove space before closing parenthesis
IF @String LIKE '% ;%'
SET @String = REPLACE(@String,' ;',';');
END
WHILE PATINDEX('%[^0-9 .;+-]%', @String) > 0 BEGIN
SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9 .;+-]%', @String), 1), '')
END
IF @String LIKE '%;'
SET @String = LEFT(@String,LEN(@String)-1);
RETURN @String;
END
GOAlso, if I want to come back from the table data to a string format, I created the function below. The matrix vector is used only if I want to add a vector at the end of each row, to be used when you apply the Gauss-Seidel method, for example.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix to String format
-- =============================================
ALTER FUNCTION [dbo].[ufnMtxToString]
(@Input AS [dbo].[uttMtxIndexed] READONLY
,@Vetor AS [dbo].[uttMtxIndexed] READONLY)
RETURNS nvarchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @MtxString nvarchar(MAX) = '';
DECLARE @i int = 1;
WHILE @i <= (SELECT MAX([col]) FROM @Input) BEGIN
DECLARE @j int = 1;
WHILE @j <= (SELECT MAX([lin]) FROM @Input) BEGIN
SET @MtxString += (SELECT CONCAT([val],' ')
FROM @Input
WHERE [lin] = @i AND
[col] = @j);
SET @j += 1;
END
IF EXISTS (SELECT 1 FROM @Vetor)
SET @MtxString += (SELECT CONCAT([val],' ')
FROM @Vetor
WHERE [col] = @i);
IF @i < (SELECT MAX([col]) FROM @Input)
SET @MtxString = TRIM(@MtxString) + ';';
ELSE
SET @MtxString = TRIM(@MtxString);
SET @i += 1;
END
RETURN @MtxString;
END
GOToolkit
Frobenius Norm
The Frobenius norm is a measure of the size or magnitude of a matrix. It is used in error measurements, matrix optimization, signal processing, control theory, and machine learning. The Frobenius norm can be thought of as the Euclidean norm of the matrix when its elements are treated as a vector. This interpretation provides a direct connection between matrix and vector norms.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Frobenius Normalization
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxFrobenius]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
DECLARE @Frob float = SQRT((SELECT SUM(SQUARE([val]))
FROM @Input));
IF @Frob > 0
UPDATE @Output
SET [val] /= @Frob;
RETURN;
END
GOData String to Format Row, Column, and Value
To avoid typing the row and column values, I decided to enter the data in a variable string using the rule to enter the values for rows separated by a space and the column separated by a semicolon. The function below transforms the values data entered in the format needed to perform matrix operations.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241110
-- Description: Data to Matrix format
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxIndexed]
(@DataValues varchar(MAX))
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
SET @DataValues = [dbo].[ufnMtxFlatCleanup] (@DataValues);
DECLARE @InputData [varchar](200)
,@Row int = 1
,@Column int
,@i int = 1
,@c numeric(18,6);
DECLARE cursorTab CURSOR FAST_FORWARD READ_ONLY FOR
SELECT value FROM string_split(@DataValues,';');
OPEN cursorTab
FETCH NEXT FROM cursorTab INTO @InputData;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Output
SELECT @Row
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,value
FROM string_split(@InputData,' ');
FETCH NEXT FROM cursorTab INTO @InputData;
SET @Row += 1;
END
CLOSE cursorTab
DEALLOCATE cursorTab
RETURN;
END
GOMatrix Cofactor
The cofactor of an element of a matrix is the determinant of the matrix obtained by excluding the row and column in the matrix that contains the element and then multiplying by POWER(-1, i+j). It is useful to find the adjoint of the matrix and its inverse.
For an element:
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241118
-- Description: Matrix Cofactor
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnMtxElementCofactor]
(@Input AS [dbo].[uttMtxIndexed] READONLY
,@iOut int
,@jOut int)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
IF @iOut IS NULL OR
@jOut IS NULL OR
(SELECT COUNT(*)
FROM @Input) = 0
RETURN NULL;
DECLARE @Output AS [dbo].[uttMtxIndexed];
INSERT INTO @Output
SELECT ROW_NUMBER() OVER (PARTITION BY [col] ORDER BY [lin], [col]) AS [lin]
,ROW_NUMBER() OVER (PARTITION BY [lin] ORDER BY [col], [lin]) AS [col]
,[val]
FROM @Input
WHERE [lin] <> @iout AND
[col] <> @jout;
DECLARE @Det float = POWER(-1,@iOut + @jOut) * [dbo].[ufnMtxDeterminant] (@Output);
RETURN @Det;
END
GOFor a matrix:
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241118
-- Description: Matrix Max Score Normalization
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxCofactor]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] = [dbo].[ufnMtxElementCofactor] (@Input,[lin],[col]);
RETURN;
END
GOMatrix Determinant
I will use direct calculation for 2×2 matrices, the cofactor method for 3×3 matrices, and if greater, the upper decomposition method.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241118
-- Description: Matrix determinant
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnMtxDeterminant]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Rows int = (SELECT MAX([lin]) FROM @Input);
IF (SELECT MAX([col]) FROM @Input) <> @Rows
RETURN NULL;
DECLARE @MtxU AS [dbo].[uttMtxIndexed]
,@Det float = 1;
IF @Rows = 2 BEGIN
SELECT @Det *= [val]
FROM @Input
WHERE [lin] = [col];
DECLARE @Minus float = 1;
SELECT @Minus *= [val]
FROM @Input
WHERE [lin] <> [col];
SET @Det -= @Minus;
END
ELSE IF @Rows = 3 BEGIN
SET @Det = (SELECT SUM([dbo].[ufnMtxElementCofactor] (@Input,[lin],[col]) * [val])
FROM @Input
WHERE [lin] = 1);
END
ELSE IF @Rows > 3 BEGIN
INSERT INTO @MtxU
SELECT [lin]
,[col]
,[val]
FROM @Input;
DECLARE @i smallint = 1;
WHILE @i <= (SELECT MAX([lin]) FROM @MtxU) BEGIN
DECLARE @ValR1 float =
(SELECT [val]
FROM @MtxU
WHERE [lin] = @i AND
[col] = @i);
IF @ValR1 = 0
RETURN NULL;
DECLARE @j smallint = @i + 1;
WHILE @j <= (SELECT MAX([lin]) FROM @MtxU) BEGIN
DECLARE @ValR2 float =
(SELECT -[val] / @ValR1
FROM @MtxU
WHERE [lin] = @j AND
[col] = @i);
UPDATE @MtxU
SET [val] =
(SELECT [val]
FROM [dbo].[tvfMtxMathRowOp] (@MtxU,@i,@j,@ValR2) X
WHERE X.col = [@MtxU].[col] AND
X.lin = @j)
WHERE [lin] = @j;
SET @j += 1;
END
SET @i += 1;
END
SELECT @Det *= [val]
FROM @MtxU
WHERE [lin] = [col];
END
RETURN @Det;
END
GOMatrix Inverse
The inverse of a squared matrix is a matrix that, when multiplied by its original one, results in its Identity matrix. There are many applications of Matrix inverse: linear system of equations, computer graphics, cryptography, machine learning, control systems, economics, etc.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241115
-- Description: Matrix Inverse
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxInverse]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @MtxL AS [dbo].[uttMtxIndexed]
,@MtxLInv AS [dbo].[uttMtxIndexed]
,@MtxU AS [dbo].[uttMtxIndexed]
,@MtxUInv AS [dbo].[uttMtxIndexed]
,@Vector AS [dbo].[uttMtxIndexed]
,@MmtIxO AS [dbo].[uttMtxIndexed];
IF (SELECT MAX([col]) FROM @Input) <> (SELECT MAX([lin]) FROM @Input) BEGIN
RETURN;
END
INSERT INTO @MtxU
SELECT [lin]
,[col]
,[val]
FROM @Input;
INSERT INTO @MtxL
SELECT [lin]
,[col]
,CASE WHEN [lin] = [col] THEN 1 ELSE 0 END
FROM @Input;
DECLARE @i smallint = 1;
WHILE @i <= (SELECT MAX([lin]) FROM @MtxU) BEGIN
DECLARE @ValR1 float =
(SELECT [val]
FROM @MtxU
WHERE [lin] = @i AND
[col] = @i);
IF @ValR1 = 0
RETURN;
DECLARE @j smallint = @i + 1;
WHILE @j <= (SELECT MAX([lin]) FROM @MtxU) BEGIN
DECLARE @ValR2 float =
(SELECT -[val] / @ValR1
FROM @MtxU
WHERE [lin] = @j AND
[col] = @i);
UPDATE @MtxU
SET [val] =
(SELECT [val]
FROM [dbo].[tvfMtxMathRowOp] (@MtxU,@i,@j,@ValR2) X
WHERE X.col = [@MtxU].[col] AND
X.lin = @j)
WHERE [lin] = @j;
UPDATE @MtxL
SET [val] = -@ValR2
WHERE [lin] = @j AND
[col] = @i;
SET @j += 1;
END
SET @i += 1;
END
SET @i = 1;
WHILE @i <= (SELECT MAX([lin]) FROM @MtxU) BEGIN
DECLARE @VectorStr nvarchar(MAX) = REPLICATE('0 ',(SELECT MAX([lin]) FROM @MtxU));
SET @VectorStr = TRIM(STUFF(@VectorStr, 2 * @i - 1, 1, N'1'));
INSERT INTO @Vector
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] (@VectorStr);
INSERT INTO @MtxUInv
SELECT Xi,@i AS Xj,Val
FROM [dbo].[tvfGaussSeidel] ((SELECT [dbo].[ufnMtxToString] (@MtxU,@Vector)));
INSERT INTO @MtxLInv
SELECT Xi,@i AS Xj,Val
FROM [dbo].[tvfGaussSeidel] ((SELECT [dbo].[ufnMtxToString] (@MtxL,@Vector)));
DELETE FROM @Vector;
SET @i += 1;
END
INSERT INTO @Output
SELECT * FROM [dbo].[tvfMtxMtxMult] (@MtxUInv,@MtxLInv);
RETURN;
END
GOMathematical Row Operations
Matrix row operations are fundamental techniques in algebra for solving system of linear equations, finding inverses of matrices, finding determinants, Eigenvalue computation, and numerical analysis. These operations are performed to manipulate rows without altering the solution of a system of equations.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Row Math Operation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMathRowOp]
(@DataValues AS [dbo].[uttMtxIndexed] READONLY
,@RowFrom smallint
,@RowTo smallint
,@Number float)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF @RowFrom < 0 OR
@RowTo < 0 OR
ISNUMERIC(@Number) = 0 OR
(SELECT COUNT(*)
FROM @DataValues) = 0
RETURN;
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @DataValues;
UPDATE @Output
SET [val] += (SELECT @Number * [val]
FROM @Output X
WHERE X.lin = @RowFrom AND
X.col = [@Output].[col])
WHERE [lin] = @RowTo;
RETURN;
END
GOMatrix Scalar
Scalar operations in matrixes are simple and efficient. They are used in matrix factorization, eigenvalue problems, linear transformation in changing dimensions or units, image processing (to adjust brightness), signal processing (to amplify or attenuate signals), statistical analysis, machine learning, physics, engineering, etc. It works by supplying the data values in the format of a table, the operator [*/+-], and the scale number.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Row Mult By Number
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMathScalar]
(@DataValues AS [dbo].[uttMtxIndexed] READONLY
,@Operator char(1)
,@Number float)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF @Operator NOT LIKE '[*/+-]' OR
(SELECT COUNT(*)
FROM @DataValues) = 0
RETURN;
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @DataValues;
IF @Operator = '*'
UPDATE @Output
SET [val] *= @Number;
ELSE IF @Operator = '/' AND @Number <> 0
UPDATE @Output
SET [val] /= @Number;
ELSE IF @Operator = '+'
UPDATE @Output
SET [val] += @Number;
ELSE IF @Operator = '-'
UPDATE @Output
SET [val] -= @Number;
RETURN;
END
GOMatrices Addition
This is an operation of adding two matrices by adding their corresponding elements. The matrices must have the same dimensions, meaning the same number of rows and columns. It is used in linear systems, transformation operations, machine learning, data science, and simulating physical systems.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix x Matrix multiplication
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMtxAdd]
(@MatrixA AS [dbo].[uttMtxIndexed] READONLY
,@MatrixB AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([lin]) FROM @MatrixA) <> (SELECT MAX([lin]) FROM @MatrixB) OR
(SELECT MAX([col]) FROM @MatrixA) <> (SELECT MAX([col]) FROM @MatrixB)
RETURN;
INSERT INTO @Output
SELECT A.lin
,B.col
,(A.val + B.val)
FROM @MatrixA A JOIN
@MatrixB B ON
A.lin = B.lin AND
A.col = B.col;
RETURN;
END
GOMatrices Division
It is a multiplication operation by the inverse of the second matrix. It is used in engineering, statistics, cryptography, computer science, machine learning, and physics.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix x Matrix multiplication
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMtxDiv]
(@MatrixA AS [dbo].[uttMtxIndexed] READONLY
,@MatrixB AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([col]) FROM @MatrixA) <> (SELECT MAX([lin]) FROM @MatrixB)
RETURN;
DECLARE @MatrixBInv AS [dbo].[uttMtxIndexed];
INSERT INTO @MatrixBInv
SELECT *
FROM [dbo].[tvfMtxInverse] (@MatrixB);
INSERT INTO @Output
SELECT A.lin
,B.col
,SUM(A.val * B.val)
FROM @MatrixA A JOIN
@MatrixBInv B ON
A.col = B.lin
GROUP BY A.lin, B.col;
RETURN;
END
GOMatrices Hadamard Product
An element-wise product, it takes two matrices of the same dimension and returns a matrix of the multiplied corresponding elements.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241121
-- Description: Matrix x Matrix Hadamard
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMtxHadamard]
(@MatrixA AS [dbo].[uttMtxIndexed] READONLY
,@MatrixB AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([lin]) FROM @MatrixA) <> (SELECT MAX([lin]) FROM @MatrixB) OR
(SELECT MAX([col]) FROM @MatrixA) <> (SELECT MAX([col]) FROM @MatrixB)
RETURN;
INSERT INTO @Output
SELECT A.lin
,B.col
,(A.val * B.val)
FROM @MatrixA A JOIN
@MatrixB B ON
A.lin = B.lin AND
A.col = B.col;
RETURN;
END
GOMatrices Multiplication
This operation produces a new matrix, and it involves taking the dot product of rows of the first matrix with columns of the second matrix. It can only be performed if the number of columns of the first matrix is equal to the number of rows of the second matrix. It is used in linear system, computer graphics, machine learning, data processing, and physics simulation.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix x Matrix multiplication
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMtxMult]
(@MatrixA AS [dbo].[uttMtxIndexed] READONLY
,@MatrixB AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([col]) FROM @MatrixA) <> (SELECT MAX([lin]) FROM @MatrixB)
RETURN;
INSERT INTO @Output
SELECT A.lin
,B.col
,SUM(A.val * B.val)
FROM @MatrixA A JOIN
@MatrixB B ON
A.col = B.lin
GROUP BY A.lin, B.col;
RETURN;
END
GOMatrices Subtraction
Matrices subtraction is an operation of subtracting one matrix from another by subtracting their corresponding elements. The matrices must have the same dimensions, meaning the same number of rows and columns. It is used in linear systems, transformation operations, machine learning, and data comparison.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix x Matrix subtraction
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMtxSub]
(@MatrixA AS [dbo].[uttMtxIndexed] READONLY
,@MatrixB AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([lin]) FROM @MatrixA) <> (SELECT MAX([lin]) FROM @MatrixB) OR
(SELECT MAX([col]) FROM @MatrixA) <> (SELECT MAX([col]) FROM @MatrixB)
RETURN;
INSERT INTO @Output
SELECT A.lin
,B.col
,(A.val - B.val)
FROM @MatrixA A JOIN
@MatrixB B ON
A.lin = B.lin AND
A.col = B.col;
RETURN;
END
GOMatrix Power
The concept of matrix powers involves raising a square matrix to an integer power n. I only did for power as integer, if negative is the same as the positive power of the matrix inversed. It is used in physics, engineering, computer science, and economics.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241115
-- Description: Matrix power
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxPower]
(@Input AS [dbo].[uttMtxIndexed] READONLY
,@Power smallint)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([col]) FROM @Input) <> (SELECT MAX([lin]) FROM @Input)
RETURN;
DECLARE @Count smallint = 1
,@MatrixA AS [dbo].[uttMtxIndexed];
INSERT INTO @Output
SELECT *
FROM @Input;
IF @Power = 0
UPDATE @Output
SET [val] = CASE WHEN [lin] = [col] THEN 1 ELSE 0 END;
ELSE IF @Power = 1
RETURN
ELSE IF @Power > 1 BEGIN
INSERT INTO @MatrixA
SELECT *
FROM @Input;
WHILE @Count < @Power BEGIN
UPDATE @MatrixA
SET [val] = X.val
FROM [dbo].[tvfMtxMtxMult] (@MatrixA,@Input) X
WHERE [@MatrixA].[lin] = X.lin AND
[@MatrixA].[col] = X.col;
SET @Count += 1;
END
DELETE FROM @Output;
INSERT INTO @Output
SELECT *
FROM @MatrixA;
END
ELSE IF @Power < 0 BEGIN
INSERT INTO @MatrixA
SELECT *
FROM [dbo].[tvfMtxInverse] (@Input);
DELETE FROM @Output;
INSERT INTO @Output
SELECT *
FROM [dbo].[tvfMtxPower] (@MatrixA,ABS(@Power))
END
RETURN;
END
GONormalization By the Maximum Value
It is a column-wise method to convert all its elements to a proportional scale relative to the largest column value, transforming data within the range [0,1]. It is used to prepare datasets for machine learning algorithms.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Max Score Normalization
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxNormMax]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
DECLARE @MAX AS [dbo].[uttMtxIndexed];
INSERT INTO @MAX
SELECT 1
,[col]
,MAX([val])
FROM @Output
GROUP BY [col];
IF NOT EXISTS
(SELECT 1
FROM @MAX
WHERE [val] = 0)
UPDATE @Output
SET [val] /=
(SELECT MAX([val])
FROM @Input X
WHERE X.col = [@Output].[col]);
RETURN;
END
GONormalization By the Maximum and Minimum Values
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241125
-- Description: Matrix Max Min Normalization
-- =============================================
ALTER FUNCTION [dbo].[tvfMtxNormRange]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
DECLARE @Range AS [dbo].[uttMtxIndexed];
INSERT INTO @Range
SELECT 1
,[col]
,MAX([val])-MIN([val])
FROM @Output
GROUP BY [col];
IF NOT EXISTS
(SELECT 1
FROM @Range
WHERE [val] = 0) BEGIN
WITH CteNormal AS
(SELECT [col] j
,MAX([val])-MIN([val]) vRange
,MIN([val]) vMin
FROM @Input
GROUP BY [col])
UPDATE @Output
SET [val] = ([val] - vMin) / vRange
FROM CteNormal
WHERE [col] = j;
END;
RETURN;
ENDRectified Linear Unit Activation (ReLU)
ReLU is the most used activation function in neural networks, in special in deep learning models. It introduces non-linearity to the model while maintaining computational efficiency.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix ReLU Activation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxReLU]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] = 0
WHERE [val] < 0;
RETURN;
END
GORectified Linear Unit Activation (ReLU) Derivative
This is essential in the context of backpropagation during the training of neural networks. ReLU Derivative is used to calculate the gradient of the loss function with respect to the weights of the network, enabling weight updates.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix ReLUDerivative Activation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxReLUDerivative]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] = CASE WHEN [val] < 0 THEN 0 ELSE 1 END;
RETURN;
END
GOSigmoid
Sigmoid is a mathematical function that maps any number into the range [0,1], where large positive inputs tend to 1 and large negative inputs tend to 0. Applied to matrices, the function operates element-wise. It is used in machine learning, neural networks, and computational biology. It is not suitable for very large or very small values because gradients become close to zero, slowing learning in deep neural networks, and can lead to slower convergence during training.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Sigmoid Activation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxSigmoid]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] = 1.00 / (1.00 + EXP(-[val]));
RETURN;
END
GOHyperbolic Tangent Activation (Tanh)
It is a smooth, differentiable function in neural networks that outputs values in the range [−1,1], which makes it well-suited for tasks where the output needs to be centered around zero. It is used in deep learning, sequence modeling, and image processing.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Tanh Activation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxTanh]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] = (EXP([val]) - EXP(-[val])) / (EXP([val]) + EXP(-[val]));
RETURN;
END
GOMatrix Transpose
The Matrix Transpose operation flips a matrix over its diagonal, converting rows into columns and vice versa. It is used in linear algebra, neural networks, data science, and machine learning.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Transpose
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxTranspose]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
DECLARE @i int = 1;
WHILE @i <= (SELECT MAX([col]) FROM @Input) BEGIN
DECLARE @j int = 1;
WHILE @j <= (SELECT MAX([lin]) FROM @Input) BEGIN
UPDATE @Output
SET [val] =
(SELECT CONCAT([val],' ')
FROM @Input
WHERE [lin] = @j AND
[col] = @i)
WHERE [lin] = @i AND
[col] = @j;
SET @j += 1;
END
SET @i += 1;
END
RETURN;
END
GOZ-score Normalization
This technique rescales data so that it has a mean of 0 and a standard deviation of 1. It is used in machine learning and statistics to normalize the features of a dataset. It is sensitive to outliers.
By column:
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Z-score Col Normalization
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxZScoreByCol]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] -=
(SELECT AVG([val])
FROM @Input X
WHERE X.col = [@Output].[col]);
UPDATE @Output
SET [val] /=
(SELECT STDEVP([val])
FROM @Input X
WHERE X.col = [@Output].[col]);
RETURN;
END
GOBy row:
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix Z-Score Row Normalization
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxZScoreByRow]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
UPDATE @Output
SET [val] -=
(SELECT AVG([val])
FROM @Input X
WHERE X.lin = [@Output].[lin]);
UPDATE @Output
SET [val] /=
(SELECT STDEVP([val])
FROM @Input X
WHERE X.lin = [@Output].[lin]);
RETURN;
END
GOGauss-Seidel method
For more details about it please see my other post at MSSQLTips – Gauss-Seidel method.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241101
-- Description: Gauss-Seidel Linear equation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfGaussSeidel]
(@DataValues varchar(MAX))
RETURNS @Solut TABLE
(Xi int
,Val decimal(18,6))
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @InputData [varchar](200)
,@Row int = 1
,@Column int
,@i int = 1
,@c numeric(18,6);
DECLARE @DataRaw
TABLE (lin int
,col int
,val numeric(18,6));
DECLARE cursorTab CURSOR FAST_FORWARD READ_ONLY FOR
SELECT value FROM string_split(@DataValues,';');
OPEN cursorTab
FETCH NEXT FROM cursorTab INTO @InputData;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @DataRaw
SELECT @Row
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,value
FROM string_split(@InputData,' ');
FETCH NEXT FROM cursorTab INTO @InputData;
SET @Row += 1;
END
CLOSE cursorTab
DEALLOCATE cursorTab
SELECT @Row = MAX(lin)
,@Column = MAX(col)
FROM @DataRaw;
WHILE @i <= @Row BEGIN
SELECT @c = val
FROM @DataRaw
WHERE lin = @i AND
lin = col;
UPDATE @DataRaw
SET val /= @c
WHERE lin = @i AND
@c <> 0;
UPDATE @DataRaw
SET val -= (SELECT val FROM @DataRaw Q WHERE lin = @i AND q.col = [@DataRaw].col) *
(SELECT val FROM @DataRaw W WHERE col = @i AND w.lin = [@DataRaw].lin)
WHERE lin <> @i;
SET @i += 1;
END
INSERT INTO @Solut
SELECT lin
,val
FROM @DataRaw
WHERE col = @Column;
RETURN;
END
GOExamples
Matrix Multiplication by Scalar 10
DECLARE @DataValues varchar(MAX) = '1 2;3 4'
,@Matrix AS [dbo].[uttMtxIndexed]
,@Output AS [dbo].[uttMtxIndexed]
,@Vector AS [dbo].[uttMtxIndexed];
INSERT INTO @Matrix
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] (@DataValues);
INSERT INTO @Output
SELECT *
FROM [dbo].[tvfMtxMathScalar] (@Matrix,'*',10);
-- Results in the format row, column, and value ======================
SELECT *
FROM @Output;
-- Results in the string format ======================================
SELECT [dbo].[ufnMtxToString] (@Output,@Vector) AS MultByScalar;
GOResult in:
Matrix Division
In this example, I will use the same matrix for both.
DECLARE @MatrixA AS [dbo].[uttMtxIndexed]
,@MatrixB AS [dbo].[uttMtxIndexed];
INSERT INTO @MatrixA
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] ('1 2;3 4');
INSERT INTO @MatrixB
SELECT *
FROM @MatrixA;
SELECT * FROM [dbo].[tvfMtxMtxDiv] (@MatrixA, @MatrixB);
GO Once matrix A and B are identical and the division is the multiplication by the transposed second matrix, the result must be an identity matrix, as you can see.
Z-score
DECLARE @DataValues varchar(MAX) = '1 2;3 4'
,@Input AS [dbo].[uttMtxIndexed];
INSERT INTO @Input
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] (@DataValues);
SELECT * FROM [dbo].[tvfMtxZScoreByCol] (@Input);
SELECT * FROM [dbo].[tvfMtxZScoreByRow] (@Input);
GOResulting in:
Next Steps
- These are the most commonly used operations in matrix algebra, although there are many others.
- STANFORD – Matrix operations
- WIKIPEDIA – Matrix addition
- WIKIPEDIA – Matrix multiplication
- WIKIPEDIA – Matrix transpose
- WIKIPEDIA – Rectifier neural networks
- Matrix Multiplication Calculated with T-SQL
- Calculating Matrix Inverse in SQL Server
Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry. Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.
- MSSQLTips Awards
- Author of the Year – 2025
- Trendsetter (25+ tips) – 2025
- Rookie of the Year – 2024
Hello Nasser Azimi, thanks a lot to point out that was missing this one, sorry about that. I am fixing it. Regards, Sebastião
missing object “tvfGaussSeidel”
The code has been added to the article.