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;
ENDSolving 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
Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry. Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.
- MSSQLTips Awards
- Author of the Year – 2025
- Trendsetter (25+ tips) – 2025
- Rookie of the Year – 2024
