How to Calculate Determinant of a NxN Matrix via MS SQL Server

Purpose: Calculation of determinant, adjoint and inverse of a matrix is essential when conducting multivariate analysis. We first discuss how to leverage function in MS SQL Server to get the determinant value of a NxN matrix.

How to calculate Determinant of a Matrix

Step:

1. Define a table type for the variable stored in the form of table.

CREATE TYPE MatrixTableType AS TABLE
(
    i int,
    j int,
    value float
)
Go

2. Create a function called [Matrix].[determinant_temp_function], which will be used in our main function. This function is to help us redefine the row and col number after selecting the first element of each row and multiply it by the decreasing matrix (removing the row and column where the selected first element) with alternative sign (starting from the positive).

CREATE FUNCTION [Matrix].[determinant_temp_function]
(
  @x AS INT,
  @y AS INT,
  @input AS Matrix.MatrixTableType READONLY
) 
RETURNS @output TABLE   
(  
     i INT,
     j INT,
     value FLOAT
)  
AS  
BEGIN 

INSERT @output
SELECT DENSE_RANK() OVER (ORDER BY  A.i ASC) AS i, DENSE_RANK() OVER (ORDER BY  A.j ASC) AS j, A.value
FROM @input AS A
WHERE A.i <> @x and A.j <> @y
RETURN  

END

Note: After removing the row and column the first element, for example of a 3x3 matrix, removing the first row and column where X11 resides in, the remaining will be X22, X23, X32 and X33. We would like to convert these remaining elements into X11, X12, X21 and X22 because we will have to calculate the determinant value of this new decreasing matrix. We can use DENSE_RANK() instead of RANK() for i and j individually to ensure the same values have same ranking.

3. Create a function called [Matrix].[determinant] to return the determinant value for the input of table with the structure we defined in the step 1.

- If the input matrix is a 1X1 one, its value will be returned.
- The function will sent the decreasing matrix to the function [Matrix].[determinant_temp_function] to redefine the number of column and row to be a new input matrix (Step2).
- The function is recursive and call itself by passing the new input matrix created by  the function [Matrix].[determinant_temp_function].
- @value_f is the first element of each row.

CREATE FUNCTION [Matrix].[determinant]
(
      @input AS Matrix.MatrixTableType READONLY
)
RETURNS FLOAT
AS
BEGIN

DECLARE @i INT
DECLARE @j INT
DECLARE @iMAX INT
DECLARE @jMAX INT
DECLARE @sign INT
DECLARE @value_11 FLOAT
DECLARE @value_D FLOAT
DECLARE @value_f FLOAT
DECLARE @value_temp FLOAT
DECLARE @tableinputv Matrix.MatrixTableType

SET @value_D = 0
SET @i = 1
SET @j = 1
SET @sign = 1 
SET @iMAX = (select max(i) from @input)
SET @jMAX = (select max(j) from @input)

IF (@iMAX = 1 AND @jMAX = 1)
BEGIN
 SET @value_11 = (SELECT value FROM @input WHERE i = 1 and j = 1 )   
 GOTO Branch_End
END

WHILE (@i <= @iMAX )
BEGIN
    SET @value_f = (SELECT value FROM @input WHERE i = @i and j = 1 )
    DELETE @tableinputv
    INSERT INTO @tableinputv (i,j,value) SELECT i,j,value FROM  Matrix.[determinant_temp_function]  (@i, @j, @input) 
 SET @value_temp = (SELECT [Matrix].[determinant] (@tableinputv))
 SET @value_D = @value_D +   @value_temp * @sign * @value_f
 SET @sign = @sign * - 1
 SET @i = @i +  1
END
RETURN(@value_D)

Branch_End: RETURN(@value_11)

END;
Go

Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲