Problem
Neural networks are an important subject that mimics how the human brain works, enabling machines to learn and make decisions. Is it possible to work with them in SQL Server without external tools?
Solution
Neural networks have been used in a varied set of domains like speech recognition, person recognition, healthcare, finance, marketing, entertainment, and others. It is very important for solving complex problems, learning from data, handling non-linear relationships, and automation.
Before we get started on how we can do this in SQL Server, here are some terms and other things to be familiar with:
- Activation Function: This function decides if a node´s output should go forward in the network. It introduces non-linearity, which helps the network learn complex patterns. Examples are ReLU activation and Sigmoid functions.
- Bias: It is like a starting point or an adjustment value added to calculations. The role of bias is to shift the value produced by the activation function. Its role is similar to the role of a constant in a linear function.
- Hidden Layers: A layer of nodes (neurons) that processes input data to find patterns and relationships before passing the results to the next layer. It’s called “hidden” because it’s not directly seen in the input or output; it works behind the scenes to help the network learn.
- Input Layer: The first layer in a neural network, or where it starts. It takes the raw data you provide and passes it to the next layers for processing. Each node (neuron) in the input layer represents one feature or variable of your data.
- Learning Rate: The most important parameter once it controls how much the weights are adjusted during each step of the training.
- Output Layer: The output layer takes input from preceding hidden layers and comes to a final prediction based on the model’s learning. It is the most important layer where we get the final result.
- Training: The network learns by adjusting weights and bias.
- Transfer Function: The job of the transfer function is to combine multiple inputs into one output value so that the activation function can be applied. The transfer function calculates the output of a node by combining the inputs from a previous node, the weights, and bias.
- Prediction: The network uses what it learned to make decisions.
- Weight: It is a connection between two nodes in the network. It determines how much influence one node has on the next, and it changes the values during the learning process to improve accuracy.
It is composed of:
- Weights and Bias – Control the calculation.
- Transfer Function – Combines inputs, weights, and bias.
- Activation Function – Determines the final output and helps the network learn better.
Starting with the training phase, the neural network learns to solve a problem by analyzing data to minimize errors, recognize patterns, and generalize new data.
Steps of the Training phase include:
- Input Data: Provide the network with labeled examples (e.g., images of cats and dogs with labels “cat” or “dog”).
- Forward Pass: Data passes through the layers; weights and biases are used to make predictions.
- Compare Output: The network compares its prediction to the correct answer using a loss function to determine how wrong it was.
- Backpropagation: The network adjusts its weights and biases using an optimization algorithm to reduce the error.
- Repeat: This process is repeated for many cycles, called epochs, until the network improves.
Afterward, the Prediction phase is used to make decisions or predictions on new, unseen data with the objective of giving accurate results based on the learned patterns.
This phase includes these steps:
- Input Data: Provide new data (e.g., an image of an unknown animal).
- Forward Pass: The network processes the input using the trained weights and biases.
- Output: The network produces a prediction (e.g., “dog” or “cat”).
Neural Network in SQL Server
In the following sets of code, we look at SQL scripts to build a neural network in SQL Server.
Results Table
The first step is to create a table where the results of the training phase will be stored, and then the prediction phase will give results based on this table’s values.
/****** Object: Table [dbo].[MtxData] Script Date: 26/11/2024 11:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MtxData](
[Mtx] [nvarchar](50) NOT NULL,
[i] [smallint] NOT NULL,
[j] [smallint] NOT NULL,
[v] [float] NULL,
CONSTRAINT [PK_MtxGen] PRIMARY KEY CLUSTERED
(
[Mtx] ASC,
[i] ASC,
[j] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOEpoch and Error Table
This table will record each iteration of the epoch and the obtained total error.
CREATE TABLE [dbo].[MtxEpochError](
[Epoch] [int] NOT NULL,
[TotalError] [float] NOT NULL,
[EpochPoint] AS ([geometry]::Point([Epoch],[TotalError],(0))),
CONSTRAINT [PK_MtxEpochError] PRIMARY KEY CLUSTERED
(
[Epoch] ASC,
[TotalError] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOResults Summary Table
For each training session, the result panel is recorded on this table, showing the impact of the changes in epoch, learning rate, and hidden layer values.
CREATE TABLE [dbo].[MtxTrainSummary](
[TrainingId] [int] IDENTITY(1,1) NOT NULL,
[Predicted] [nvarchar](max) NULL,
[Expected] [nvarchar](max) NULL,
[LearningRate] [float] NULL,
[HiddenNodes] [int] NULL,
[Epoch] int NULL,
[EpochFinal] [int] NULL,
[ErrorMax] [float] NULL,
[ErrorMin] [float] NULL,
[ErrorRange] AS ([ErrorMax]-[ErrorMin]),
[Inputs] [nvarchar](max) NULL,
[Outputs] [nvarchar](max) NULL,
CONSTRAINT [PK_MtxEvolution] PRIMARY KEY CLUSTERED
(
[TrainingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOMatrix Format as a User-defined Type
To work with matrices in SQL Server, I created a User-defined table type in the format row, column, and its value.
/****** Object: UserDefinedTableType [dbo].[uttMtxIndexed] Script Date: 26/11/2024 11:51:48 ******/
CREATE TYPE [dbo].[uttMtxIndexed] AS TABLE(
[lin] [smallint] NULL,
[col] [smallint] NULL,
[val] [float] NULL
)
GOData Entry
When I did not know the number (n) of rows and the feature columns to be entered in advance, I decided to standardize the data entry in a variable string in the format of values separated by a space and rows by a semicolon. To standardize and check if it attains this rule, I created the following User-defined-functions.
String Cleanup Function
This function cleans strings and removes unwanted characters.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Cleanup String
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnMtxFlatCleanup]
(@String varchar(MAX))
RETURNS nvarchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
IF LEN(@String) = 0
RETURN '';
WHILE @String LIKE '% %' OR @String LIKE '%; %' OR @String LIKE '% ;%' BEGIN
-- Replace double spaces with a single space
IF @String LIKE '% %'
SET @String = REPLACE(@String,' ',' ');
-- Remove space after opening parenthesis
IF @String LIKE '%; %'
SET @String = REPLACE(@String,'; ',';');
-- Remove space before closing parenthesis
IF @String LIKE '% ;%'
SET @String = REPLACE(@String,' ;',';');
END
WHILE PATINDEX('%[^0-9 .;+-]%', @String) > 0 BEGIN
SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9 .;+-]%', @String), 1), '')
END
IF @String LIKE '%;'
SET @String = LEFT(@String,LEN(@String)-1);
RETURN @String;
END
GOString to Matrix Format Function
This function transforms the data entered as a string into the format of a table with row number, column number, and its value.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241110
-- Description: Data to Matrix format
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxIndexed]
(@DataValues varchar(MAX))
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
SET @DataValues = [dbo].[ufnMtxFlatCleanup] (@DataValues);
DECLARE @InputData [varchar](200)
,@Row int = 1
,@Column int
,@i int = 1
,@c 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 @Output
SELECT @Row
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,value
FROM string_split(@InputData,' ')
WHERE value NOT LIKE '%.%.%';
FETCH NEXT FROM cursorTab INTO @InputData;
SET @Row += 1;
END
CLOSE cursorTab
DEALLOCATE cursorTab
RETURN;
ENDMatrices Multiplication Function
This function produces a new matrix and involves taking the product of rows of the first matrix with columns of the second matrix.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241112
-- Description: Matrix x Matrix multiplication
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxMtxMult]
(@MatrixA AS [dbo].[uttMtxIndexed] READONLY
,@MatrixB AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
IF (SELECT MAX([col]) FROM @MatrixA) <> (SELECT MAX([lin]) FROM @MatrixB)
RETURN;
INSERT INTO @Output
SELECT A.lin
,B.col
,SUM(A.val * B.val)
FROM @MatrixA A JOIN
@MatrixB B ON
A.col = B.lin
GROUP BY A.lin, B.col;
RETURN;
END
GONormalization by Maximum and Minimum Values Function
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241125
-- Description: Matrix Max Min Normalization
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfMtxNormRange]
(@Input AS [dbo].[uttMtxIndexed] READONLY)
RETURNS @Output
TABLE (lin int
,col int
,val float)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @Output
SELECT [lin]
,[col]
,[val]
FROM @Input;
DECLARE @Range AS [dbo].[uttMtxIndexed];
INSERT INTO @Range
SELECT 1
,[col]
,MAX([val])-MIN([val])
FROM @Output
GROUP BY [col];
IF NOT EXISTS
(SELECT 1
FROM @Range
WHERE [val] = 0) BEGIN
WITH CteNormal AS
(SELECT [col] j
,MAX([val])-MIN([val]) vRange
,MIN([val]) vMin
FROM @Input
GROUP BY [col])
UPDATE @Output
SET [val] = ([val] - vMin) / vRange
FROM CteNormal
WHERE [col] = j;
END;
RETURN;
END
GOMatrix Initialization Stored Procedures
This stored procedure fills out the matrix with fractional numbers from -0.5 to +0.5 as seed values.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241110
-- Description: Matrix Randon
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspNeuralRand]
(@inputNeurons int
,@hiddenNeurons int)
WITH EXECUTE AS CALLER
AS
BEGIN;
WITH CteI AS
(SELECT TOP (@inputNeurons) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
FROM sys.objects AS o1 CROSS JOIN
sys.objects AS o2),
CteJ AS
(SELECT TOP (@hiddenNeurons) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS j
FROM sys.objects AS o1 CROSS JOIN
sys.objects AS o2)
SELECT i.i
,j.j
,CONVERT(decimal(18,8),(CONVERT(float, ABS(CHECKSUM(NEWID())) % 1000000) / 1000000) - 0.5) AS R1
FROM CteI AS i CROSS JOIN
CteJ AS j;
RETURN;
END
GOTraining Stored Procedure
This is the store procedure that conducts the Training Phase. Here, it is important to point out the fine-tuning values of Epochs, Learning rate, and Hidden nodes.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241125
-- Description: Neural Network - Training
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspNeuralNetTrain]
(@Inputs varchar(MAX)
,@Output varchar(MAX)
,@Epochs int
,@LearningRate float
,@n_hidden int
,@ShowSpatialTab bit)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- CLEAN DATA =======================================================
TRUNCATE TABLE [MtxData];
IF NOT EXISTS
(SELECT 1
FROM [dbo].[MtxTrainSummary]
WHERE [Inputs] = @Inputs AND
[Outputs] = @Output)
TRUNCATE TABLE [dbo].[MtxTrainSummary];
TRUNCATE TABLE [dbo].[MtxEpochError];
-- VARIABLES ========================================================
DECLARE @epoch int
,@i int
,@iRow int
,@n_samples int
,@n_inputs int
,@n_outputs int;
-- MATRIX VARIABLES =================================================
DECLARE @B1 AS [dbo].[uttMtxIndexed]
,@B2 AS [dbo].[uttMtxIndexed]
,@G1 AS [dbo].[uttMtxIndexed]
,@D2 AS [dbo].[uttMtxIndexed]
,@FO AS [dbo].[uttMtxIndexed]
,@HL AS [dbo].[uttMtxIndexed]
,@HO AS [dbo].[uttMtxIndexed]
,@I0 AS [dbo].[uttMtxIndexed]
,@I1 AS [dbo].[uttMtxIndexed]
,@IX AS [dbo].[uttMtxIndexed]
,@LI AS [dbo].[uttMtxIndexed]
,@OL AS [dbo].[uttMtxIndexed]
,@OY AS [dbo].[uttMtxIndexed]
,@TG AS [dbo].[uttMtxIndexed]
,@W1 AS [dbo].[uttMtxIndexed]
,@W2 AS [dbo].[uttMtxIndexed];
-- DATA LOAD ========================================================
INSERT INTO @IX
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] (@Inputs);
SELECT @n_inputs = MAX([col])
,@n_samples = MAX([lin])
FROM @IX;
INSERT INTO @OY
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] (@Output);
SELECT @n_outputs = MAX([col])
FROM @OY;
DECLARE @r_outputs int = (SELECT MAX([lin]) FROM @OY);
IF @n_samples <> @r_outputs BEGIN
PRINT(CONCAT('Number of samples in Inputs does not have its respective output value!'
,' (',@n_samples,' x ',@r_outputs,')'))
RETURN
END
-- DATA PREPARATION =================================================
-- Normalizing input ========
INSERT INTO @I0
SELECT 1
,[col]
,MIN([val])
FROM @IX
GROUP BY [col];
INSERT INTO @I1
SELECT 1
,[col]
,MAX([val])-MIN([val])
FROM @IX
GROUP BY [col];
UPDATE @IX
SET [val] = X.val
FROM [dbo].[tvfMtxNormRange] (@IX) X
WHERE [@IX].lin = X.lin AND
[@IX].col = X.col;
-- Hidden ===================
INSERT INTO @HL
EXEC [dbo].[uspNeuralRand] @n_samples,@n_hidden;
INSERT INTO @W1
EXEC [dbo].[uspNeuralRand] @n_inputs,@n_hidden;
INSERT INTO @B1
EXEC [dbo].[uspNeuralRand] @n_hidden,1;
INSERT INTO @G1
EXEC [dbo].[uspNeuralRand] @n_hidden,1;
-- Output ===================
INSERT INTO @OL
EXEC [dbo].[uspNeuralRand] @n_samples,@n_outputs;
INSERT INTO @W2
EXEC [dbo].[uspNeuralRand] @n_hidden,@n_outputs;
INSERT INTO @B2
EXEC [dbo].[uspNeuralRand] @n_outputs,1;
INSERT INTO @D2
EXEC [dbo].[uspNeuralRand] @n_samples,@n_outputs;
INSERT INTO @FO
EXEC [dbo].[uspNeuralRand] @n_outputs,1;
-- TRAINING =========================================================
SET @epoch = 1;
WHILE @epoch <= @epochs BEGIN
DECLARE @TotalError float = 0;
SET @iRow = 1;
WHILE @iRow <= @n_samples BEGIN
-- Loading input and output line ============================
DELETE FROM @LI;
INSERT INTO @LI
SELECT 1
,[col]
,[val]
FROM @IX
WHERE [lin] = @iRow;
DELETE FROM @TG;
INSERT INTO @TG
SELECT 1
,[col]
,[val]
FROM @OY
WHERE [lin] = @iRow;
-- Forward Pass - Hidden layer ==============================
DELETE FROM @HO;
INSERT INTO @HO
SELECT [col]
,[lin]
,[val]
FROM @B1;
UPDATE @HO
SET [val] +=
(SELECT [val]
FROM [dbo].[tvfMtxMtxMult] (@LI, @W1) X
WHERE X.lin = [@HO].lin AND
X.col = [@HO].col);
-- ReLU Activation ============
UPDATE @HO
SET [val] = 0
WHERE [val] < 0;
---- Forward Pass - Output layer ==============================
UPDATE @FO
SET [val] =
(SELECT [val]
FROM @B2 Y
WHERE Y.lin = [@FO].lin AND
Y.col = [@FO].col);
UPDATE @FO
SET [val] +=
(SELECT [val]
FROM [dbo].[tvfMtxMtxMult] (@HO, @W2) X
WHERE X.col = [@FO].lin AND
X.lin = [@FO].col);
-- Output error gradient ====================================
DECLARE @ErrorVal float =
(SELECT SUM(T.val - F.val)
FROM @TG T LEFT OUTER JOIN
@FO F ON
T.lin = F.col AND
T.col = F.lin);
SET @TotalError += SQUARE(@ErrorVal);
DECLARE @OutputGradient float = -2 * @ErrorVal;
-- Back propagation - Hidden to Output =====================
UPDATE @G1
SET [val] =
(SELECT SUM([val]) * @OutputGradient
FROM @W2
WHERE [@W2].lin = [@G1].lin
GROUP BY [lin]);
WITH CteHO AS
(SELECT [col] i
,[lin] j
FROM @HO
WHERE [val] <= 0)
UPDATE @G1
SET [val] = 0
FROM CteHO
WHERE [lin] = i AND
[col] = j;
-- Weights and biases =======================================
UPDATE @W1
SET [val] -= @learningRate *
(SELECT [val]
FROM @G1 G
WHERE G.lin = [@W1].col) *
(SELECT [val]
FROM @LI L
WHERE L.col = [@W1].lin);
UPDATE @B1
SET [val] -= @learningRate *
(SELECT SUM([val])
FROM @G1
WHERE [@G1].lin = [@B1].lin);
UPDATE @W2
SET [val] -= @learningRate * @OutputGradient *
(SELECT H.val
FROM @HO H
WHERE H.col = [@W2].lin);
UPDATE @B2
SET [val] -= @learningRate * @OutputGradient;
SET @iRow += 1;
END
-- Follow-up iteractions ========================================
IF @epoch % (@epochs / 10.0) = 0 BEGIN
DECLARE @totalErrorStr nvarchar(50) = @totalError;
DECLARE @epochStr nvarchar(50) = FORMAT(@epoch,'000000');
RAISERROR('Epoch %s: Error = %s', 0, 1, @epochStr, @totalErrorStr) WITH NOWAIT;
END
INSERT INTO [dbo].[MtxEpochError]
([Epoch]
,[TotalError])
VALUES (@epoch
,@TotalError);
-- Escape point based on error ==================================
IF @TotalError < 0.00001
BREAK;
SET @epoch += 1;
END
-- SAVE DATA ========================================================
INSERT INTO MtxData SELECT 'B1',* FROM @B1;
INSERT INTO MtxData SELECT 'B2',* FROM @B2;
INSERT INTO MtxData SELECT 'I0',* FROM @I0;
INSERT INTO MtxData SELECT 'I1',* FROM @I1;
INSERT INTO MtxData SELECT 'W1',* FROM @W1;
INSERT INTO MtxData SELECT 'W2',* FROM @W2;
INSERT INTO MtxData VALUES ('inputs',1,1,@n_inputs);
-- RESULT PANEL =====================================================
DECLARE @vMax float
,@vMin float;
SELECT @vMax = MAX([TotalError])
,@vMin = MIN([TotalError])
FROM [dbo].[MtxEpochError]
DECLARE @Result
TABLE ([Result] nvarchar(MAX)
,[Val] float);
DECLARE @Final nvarchar(MAX) = LEFT(@Inputs, CHARINDEX(';', @Inputs) - 1);
INSERT INTO @Result
EXECUTE [dbo].[uspNeuralNetPred] @Final;
SELECT TOP 1 @Final = [Result]
FROM @Result;
INSERT INTO [dbo].[MtxTrainSummary]
VALUES (@Final
,LEFT(@Output, CHARINDEX(';', @Output) - 1)
,@learningRate
,@n_hidden
,@Epochs
,@epoch - 1
,@vMax
,@vMin
,@Inputs
,@Output);
DECLARE @Last int = SCOPE_IDENTITY();
SELECT [TrainingId]
,[Predicted]
,[Expected]
,[LearningRate]
,[HiddenNodes]
,[Epoch]
,[EpochFinal]
,[ErrorMax]
,CASE WHEN [TrainingId] = @Last THEN '-->' ELSE '' END AS [Last]
,[ErrorMin]
,[ErrorRange]
,[Inputs]
,[Outputs]
FROM [dbo].[MtxTrainSummary]
ORDER BY [ErrorMin] DESC;
IF @ShowSpatialTab = 1
SELECT [Epoch]
,[TotalError]
,[EpochPoint]
FROM [dbo].[MtxEpochError]
WHERE [TotalError] < @epoch;
RETURN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
-- Print error information.
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line: ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH;
END
GOPrediction Stored Procedure
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20241125
-- Description: Neural Network - Prediction
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspNeuralNetPred]
(@Inputs varchar(MAX))
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- MATRIX VARIABLES =================================================
DECLARE @W1 AS [dbo].[uttMtxIndexed]
,@B1 AS [dbo].[uttMtxIndexed]
,@W2 AS [dbo].[uttMtxIndexed]
,@B2 AS [dbo].[uttMtxIndexed]
,@IX AS [dbo].[uttMtxIndexed]
,@HO AS [dbo].[uttMtxIndexed]
,@n_inputs int;
-- PREDICTION =======================================================
INSERT INTO @IX
SELECT [lin]
,[col]
,[val]
FROM [dbo].[tvfMtxIndexed] (@Inputs);
SELECT @n_inputs = MAX([col])
FROM @IX;
IF @n_inputs <>
(SELECT [v]
FROM [dbo].[MtxData]
WHERE [Mtx] = 'inputs') BEGIN
PRINT 'The number of features does not match!'
RETURN
END
-- NORMALIZE INPUT ==============================
IF NOT EXISTS
(SELECT 1
FROM [dbo].[MtxData]
WHERE [Mtx] = 'I1' AND
[v] = 0)
UPDATE @IX
SET [val] = ([val] -
(SELECT [v]
FROM [dbo].[MtxData] X
WHERE [Mtx] = 'I0' AND
[@IX].lin = X.i AND
[@IX].col = X.j)) /
(SELECT [v]
FROM [dbo].[MtxData] X
WHERE [Mtx] = 'I1' AND
[@IX].lin = X.i AND
[@IX].col = X.j);
-- RETRIEVE DATA ====================================================
INSERT INTO @W1
SELECT [i]
,[j]
,[v]
FROM [MtxData]
WHERE [Mtx] = 'W1';
INSERT INTO @W2
SELECT [i]
,[j]
,[v]
FROM [MtxData]
WHERE [Mtx] = 'W2';
INSERT INTO @B1
SELECT [i]
,[j]
,[v]
FROM [MtxData]
WHERE [Mtx] = 'B1';
INSERT INTO @B2
SELECT [i]
,[j]
,[v]
FROM [MtxData]
WHERE [Mtx] = 'B2';
-- Forward Pass - Hidden layer ======================================
DELETE FROM @HO;
INSERT INTO @HO
SELECT [col]
,[lin]
,[val]
FROM @B1;
UPDATE @HO
SET [val] +=
(SELECT [val]
FROM [dbo].[tvfMtxMtxMult] (@IX, @W1) X
WHERE X.lin = [@HO].lin AND
X.col = [@HO].col);
-- ReLU Activation ============
UPDATE @HO
SET [val] = 0
WHERE [val] < 0;
-- Forward Pass - Output layer ======================================
UPDATE @B2
SET [val] +=
(SELECT [val]
FROM [dbo].[tvfMtxMtxMult] (@HO, @W2) X
WHERE X.lin = [@B2].col AND
X.col = [@B2].lin);
-- RESULT ===========================================================
DECLARE @Results nvarchar(MAX) = '';
SELECT @Results += CONCAT([val],' ')
FROM @B2;
SELECT CONCAT('Predicted for ['
,REPLACE(@Inputs,' ',',')
,'] is ['
,TRIM(@Results)
,']') AS Result
,[val]
FROM @B2;
-- ==================================================================
RETURN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
-- Print error information.
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line: ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH;
END
GONotes
- It is very important to note that the training phase should be run one time, and if there are no changes, you can run the prediction as many times as needed. If, for any reason, you observe that it is necessary to add more inputs to improve the results, then you run the training phase again.
- Start simple with the Hidden Layer number and increment as needed for complex problems; the number is based on the performance. Remember that more layers improve the network´s ability to capture patterns; it can also lead to overfitting or when the model is very good at fitting the training data but performs poorly on new data. I used an escape point when the total error is smaller than 0.00001.
- Experimentation and tuning are important. I recommend running the examples with the Messages tab selected because then you can observe the total error evolution and stop the execution if the value is not suitable, or wait until the final processing, when the results will be added to the follow-up panel.
- Once the learning rate controls the size of the steps adjustments on weights, if it is too high will overshoot the optimal values resulting in instability and failing to converge; and if it is too low will get stuck in a suboptimal solution. The suggested value to start is like 0.01 or 0.001 and adjust from there.
- By showing the spatial results tab in SSMS, it will present the results of the table MtxEpochError, where the recorded error is smaller than the epoch value.
- The follow-up panel is just a summary of the historic trainings and the decision. If it is good enough, the adjustments are taken using the prediction tool in another query based on the results.
To verify if the tool is working correctly, we need to enter some inputs with known outputs to confirm if the computer has learned and presented the expected results.
Example 1 – Input Data Using Linear Equation
To understand how it works, I will use the input data using the formula y = 2*x + 1. The data is entered as a string (shown below). Remember that I decided to standardize all values separated by a space and the rows by a semicolon. Let’s run the following code and click on the Messages tab to follow the results.
EXECUTE [dbo].[uspNeuralNetTrain]
'17;1;25;135;710' -- Input
,'35;3;51;271;1421' -- Output
,1000 -- Epochs
,0.01 -- Learning rate
,1 -- Hidden layer
,0; -- Show Spatial results tab
GOIf you are on the Messages tab and if the first error found is problematic, you can stop the execution at will, and the training data will not be recorded.
As you can see, the expected value for x = 17 and y = 35, but the predicted value under those adjustments is y = 369. So, I changed the value for the Learning rate from 0.01 to 0.001 and executed the above stored procedure again.
Better but the total error and prediction is still high, and then I played a little with the “knobs” of my tool always executing again the training phase, after each modification.
I am happy with the results for now and I am ready to confirm if it works with new values, but now using the prediction tool in a new QUERY page. There is no need to run the training phase once the record is saved.
EXECUTE [dbo].[uspNeuralNetPred] '7'Once the prediction phase above is executed, I obtained Predicted for [7] is [15.001], which is good enough for y = 2*x +1 as the training phase figured out.
Trying with other n number running the query: EXECUTE [dbo].[uspNeuralNetPred] ‘n’
- Predicted for [27] is [55.001]
- Predicted for [10] is [21.001]
- Predicted for [0] is [2.86572]
- Predicted for [-10] is [2.86069]
Observe that for 0 (zero) and negative numbers, it fails because of the important detail of extrapolation. Neural networks are typically trained to interpolate, which means to predict within the range of training data. Neural networks generally struggle with extrapolation unless explicitly trained on such scenarios.
In my case, I will include more data to check the effect of the input on the results. Note that once the data is changed, the panel starts from zero. I will run the stored procedure without changing the feature values.
EXECUTE [dbo].[uspNeuralNetTrain]
'17;1;25;135;710;-100' -- Input
,'35;3;51;271;1421;-199' -- Output
,1000 -- Epochs
,0.0001 -- Learning rate
,2 -- Hidden layer
,0; -- Show Spatial results tab
GO
Now in another query panel, I run the command EXECUTE [dbo].[uspNeuralNetPred] ‘n’ to obtain:
- Predicted for [0] is [0.999634]
- Predicted for [-10] is [-19.0004]
- Predicted for [5000] is [10001]
Test the limits of the prediction to check if it attained your expectations, taking into consideration the interpolation range.
For this training with these features, in my scenario, it has the upper limit of 46056 and the lower limit of -100:
- Predicted for [46056] is [92112.5]
- Predicted for [46057] is [92114.5] in fact has a value of 92114.49 and is expected to be 92115
- Predicted for [-100] is [-198.997]
- Predicted for [-101] is [-199.002] is expected to be -201
The Spatial results panel in SSMS shows the evolution of total error during the iteration of epochs. I listed only the error below the epoch feature value to make it possible to plot.
Example 2 – Input Data Using Bit Values
EXECUTE [dbo].[uspNeuralNetTrain]
'0 0 1;1 1 1;1 0 1;0 1 1'
,'0;1;1;0'
,1000
,0.01
,1
,0;
GO
Which gave me the results running EXECUTE [dbo].[uspNeuralNetPred] ‘n’
- Predicted for [0,1,1] is [0.000915691]
- Predicted for [1,0,0] is [0.998033]
- Predicted for [0,1,1] is [0.000915691]
- Predicted for [1,1,1] is [0.998033]
A default rule when dealing with binary classification, the use of 0.5 as a threshold is common. So, anything below 0.5 is considered 0 (zero), and anything greater than 0.5 is considered 1 (one). But the determination of the threshold value depends on the problem requirements. For example, in medical diagnosis, missing a disease might be worse than a false alarm, so you might lower the threshold. In spam filtering, false positives might be more problematic, so you might raise the threshold.
Example 3 – Input Data Using Housing Prices
This example uses house features (Area, Bedrooms, and Age) and the prices I defined. I played with the numbers already.
EXECUTE [dbo].[uspNeuralNetTrain]
'1500 3 20;2000 4 15;2500 4 10;3000 5 5;1200 2 30'
,'1523;2019;2514;3010;1232'
,3000
,0.0001
,3
,1;
GO
EXECUTE [dbo].[uspNeuralNetPred] '2002 3 15'
GOThe results running EXECUTE [dbo].[uspNeuralNetPred] ‘— row values — ‘ are:
- Predicted for [2002,3,15] is [2020]
- Predicted for [3000,2,5] is [3006.94]
- Predicted for [1200,2,30] is [1232]
Below is the error evolution graph in SSMS Spatial results tab:
Final Comments
If you want to save the data for future use, export the table MtxData to a new table and create another prediction stored procedure [dbo].[uspNeuralNetPred] changing the inside reference to MtxData with the name of this new table. After that, use the new stored procedure as much as needed. If you run a training phase, the data of MtxData will be replaced.
This tool is a basic starting point to work with Neural Networks and certainly does not cover all details and exceptions that can occur in certain scenarios.
Improvements like the Adaptive Moment Estimation (ADAM) algorithm can be adopted to improve the adjustments in the weights in such a way that makes the model learn faster and more efficiently. You can also implement a step decay in the learning rate based on the epoch value using ADAM.
Also, consider other improvements like the adoption of regularization techniques, data augmentation for small datasets, and others.
Another essential point to develop is the Validation process.
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
Update: It looks like the table definition for MtxTrainSummary is incorrect.
The solution below appears to fix the issue.
CREATE TABLE [dbo].[MtxTrainSummary](
[TrainingId] [int] IDENTITY(1,1) NOT NULL,
[Predicted] [nvarchar](max) NULL,
[Expected] [nvarchar](max) NULL,
[LearningRate] [float] NULL,
[HiddenNodes] [int] NULL,
[Epoch] int NULL, –Added Geoff Langdon.
[EpochFinal] [int] NULL,
[ErrorMax] [float] NULL,
[ErrorMin] [float] NULL,
[ErrorRange] AS ([ErrorMax]-[ErrorMin]),
[Inputs] [nvarchar](max) NULL,
[Outputs] [nvarchar](max) NULL,
CONSTRAINT [PK_MtxEvolution] PRIMARY KEY CLUSTERED
(
[TrainingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Hello Geoffrey, you are right it was missing exactly the line [Epoch] int NULL. Sorry about that and thanks a lot to point it out!
Thanks Geoffrey the article has been updated.
Error in NeuralNetTrain:
Msg 8101, Level 16, State 1, Procedure uspNeuralNetTrain, Line 329 [Batch Start Line 0]
An explicit value for the identity column in table ‘dbo.MtxTrainSummary’ can only be specified when a column list is used and IDENTITY_INSERT is ON.