Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Matrix Multiplication Calculated with T-SQL


By:   |   Last Updated: 2018-11-23   |   Comments (1)   |   Related Tips: More > T-SQL

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:

matrix multiplication

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:

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
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.

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.


Last Updated: 2018-11-23


next webcast button


next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 great example


Learn more about SQL Server tools