Matrix Multiplication Calculated with T-SQL

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


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


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:

matrix multiplication

Here is the definition (from the Wikipedia):

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:


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 
SELECT A.I, B.J, SUM (A.val * B.val) AS val 
FROM @matA A,@matB B 

Example Execution

Let's say we have two matrixes as follows:

matrix a and matrix b data

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.

calculate matrix

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

result set matrix

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
VALUES (1, 1, -1), (1, 2, 0), (1, 3, 6), 
       (2, 1,  2), (2, 2, 5), (2, 3, 1);
-- filling second matrix
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.

result set data

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

result set matrix
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

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

View all my tips

Comments For This Article

Friday, November 23, 2018 - 3:25:42 AM - gaga Back To Top (78310)

 great example

get free sql tips
agree to terms