Gauss-Seidel Method SQL Function to Solve Linear Equations

Problem

Solving linear equations is essential for solving real-world problems in Science, Engineering, Data Analysis, Machine Learning, Economics, Finance, and other areas. Is it possible to have a tool to solve linear equations directly in SQL Server? We will look at how to create a Gauss-Seidel method function for SQL Server.

Solution

In numerical linear algebra, the Gauss–Seidel method, also known as the Liebmann method or the method of successive displacement, is an iterative method used to solve a system of linear equations described as a collection of two or more linear equations involving the same variables.

The equation: A * x = b represents the solution where:

• A is a square matrix composed by a system of n linear equations,
• x is the unknown vector of variables, and
• b is the vector of constants.

Rule to Enter Data

I did not know how many linear equations would be entered, so I decided to normalize the process of entering the initial data in the following format: ‘A(1,1) A(1,2) A(1,3) b(1); A(2,1) A(2,2) A(2,3) b(2); A(1,1) A(1,2) A(1,3) b(3)’

Note: The values of A are separated by a space and it adds the respective b value for the row that is separated by a semicolon.

SQL Function for Gauss–Seidel Method

The function to calculate the vector x is:

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20241101
-- Description: Gauss-Seidel Linear equation
-- =============================================
CREATE 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

Solving Linear Equations Using Gauss-Seidel Method Examples

Example 1

I found an example using Python code for Gauss-Seidel Method:

Let’s solve this using the SQL function.

Type the data, ignore everything but numbers, for each row separated by a semicolon.

SELECT * FROM [dbo].[tvfGaussSeidel] ('8 3 -3 14;-2 -8 5 5;3 5 10 -8');

The result will be:

Example 2

Here is an example from WIKIPEDIA – Gauss-Seidel method:

Typing the data according to our rule as follows. For any missing values, enter a value of zero.

SELECT * FROM [dbo].[tvfGaussSeidel] ('10 -1 2 0 6;-1 11 -1 3 25;2 -1 10 -1 -11;0 3 -1 8 15');

Here are the results:

Using the Gauss–Seidel method is a good technique when solving this type of equation.

Next Steps

• WIKIPEDIA – System of linear equations
• More SQL User Defined Functions