By: Eli Leiba | Comments (1) | Related: > TSQL

##### Problem

I need to perform matrix multiplication in T-SQL for one of my applications. Is this possible with T-SQL code in SQL Server?

##### Solution

The term "matrix multiplication" or sometimes called "matrix product" is a binary operation (an operation between two matrices) that produces a matrix from two matrices in the following manner:

Here is the definition (from the Wikipedia): https://en.wikipedia.org/wiki/Matrix_multiplication.

## Matrix Multiplication with T-SQL

The stored procedure in this tip is a suggested T-SQL solution to this operation and does the mathematical operations by using the T-SQL equivalent methods. My solution involves creating a rather simple T-SQL stored procedure in a SQL Server application database, called dbo.multMatrixes that will get the following two parameters of type matrixType: @matA and @matB. These are two tables valued parameters (TVP) that represent the two matrices in T-SQL.

#### Create New Type

We need to create a new type in SQL Server and the matrix type is defined as follows:

CREATE TYPE matrixType AS TABLE (i INT,j INT, val REAL) GO

This type represents a matrix where V [i,j] equals the value of the cell in row 'i' and column 'j'.

i = 1,2,3,...n and j = 1,2,3,…m for the first matrix and 1,2,3,…m and 1,2,3,...p for the second matrix respectively.

The procedure does a join on these 2 tables matching j (matrix A) to i (matrix B) grouped by i (in matrix A) and j (in matrix B) and sums the product of the values giving the multiplied matrix as the result set.

#### SQL Server Stored Procedure to Calculate Matrix Multiplication

-- ================================================================================================= -- Author: Eli Leiba -- Create date: 11-2018 -- Procedure Name: dbo.multMatrixes -- Description: -- The procedure gets 2 parameters of type matrixType: @matA and @matB -- The procedure multiplies the two matrixes by doing the mathematical operation. -- It joins the tables matching j (matrix A) to i (matrix B), grouped by i (matrix A) and j (matrix B) and -- sums the product of the values. -- ================================================================================================= CREATE PROCEDURE dbo.multMatrixes @matA matrixType READONLY, @matB matrixType READONLY AS SELECT A.I, B.J, SUM (A.val * B.val) AS val FROM @matA A,@matB B WHERE A.J = B.I GROUP BY A.I, B.J; GO

## Example Execution

Let's say we have two matrixes as follows:

Here is how the final matrix is calculated.

For each row in Matrix A we multiply this against each column in Matrix B. The values shaded in gray are the final values for the matrix which we will see below.

This is what Matrix C would look like using the numbers from above.

Below is the T-SQL script for populating the matrices and completing the multiplication. For the values, the first position is the row, the second is the column and the third is the value.

- For Matrix A, for row 1, column 1 the value is -1, this would be represented as (1, 1, -1)
- For Matrix A, for row 1, column 2 the value is -0, this would be represented as (1, 2, 0)
- etc...

DECLARE @matrixA matrixType; DECLARE @matrixB matrixType; -- filling first matrix INSERT INTO @matrixA VALUES (1, 1, -1), (1, 2, 0), (1, 3, 6), (2, 1, 2), (2, 2, 5), (2, 3, 1); -- filling second matrix INSERT INTO @matrixB VALUES (1, 1, 2), (1, 2, -4) ,(1, 3, 9), (2, 1, 0), (2, 2, 5) ,(2, 3, 1), (3, 1, 3), (3, 2, 6) ,(3, 3, -1); -- now doing the matrixes multiplication EXEC dbo.multMatrixes @matrixA, @matrixB

This returns 6 rows as follows. Column I is the row and column J is the column and val is the computed value.

If we pivot the data back to the matrix, we would have the following:

##### Next Steps

- You can create and compile this simple procedure (and type) in your application database and use it as a simple T-SQL tool for multiplying matrices.
- The procedure should work for SQL Server version 2008 and above (2012, 2014, 2016 and 2017) since table valued parameters for stored procedure had been introduced in SQL Server 2008.

##### About the author

This author pledges the content of this article is based on professional experience and not AI generated.

**View all my tips**