SOFTMAX Function in SQL Server

Problem

The SOFTMAX function takes raw scores and converts into a probability distribution. This mathematical function is used in neural networking training, multiclass classification methods, multinomial logistic regression, multiclass linear discriminant analysis, and naïve Bayes classifiers. How can this function be built in SQL Server?

Solution

Softmax is used anytime you need to transform scores into probabilities and the function is defined as:

Important notes:

• The Softmax function converts values in numbers between 0 and 1 and when summed up the total is 1.
• If any value compared with another one is greater, then its Softmax will be greater also.
• Once we use exponentiation all the values will be positive outputs and this is the most important part of using it once simple normalization fails on this aspect.
• Exponential amplify differences between scores. If one score is slighter than another the difference will be more pronounced creating more decisive distributions. Pay attention only for the overconfidence effect of these amplifications.
• The Temperature (T) controls the confidence, using lower values will sharpen the distribution concentrating the probability in the top choices producing predictable outputs, and higher temperatures will flatten the distribution, making all options more equally like, introducing variety.
• If you have very large scores that can cause overflows, like numbers greater than 1000, then it is a good technique to normalize the data subtracting the maximum value for all elements, keeping the proportions, not changing the final probabilities.
• Another important aspect to pay attention is the Softmax bottleneck with refers to the fact that the expressivity of Softmax-based language models is limited by the rank of the weight matrix in the final layer when a mixture of Softmaxes is indicated to address this issue that appears in the natural language contexts that has complex dependencies.

Softmax SQL Function

First of all, we need to create a User-defined table type.

-- MSSQLTips (T-SQL)
CREATE TYPE [dbo].[uttListFloat] AS TABLE(
    [i] [int] NULL,
    [val] [float] NULL
)
GO

Then the Softmax function:

-- =============================================
-- Author:        SCP - MSSQLTips
-- Create date:    20250924
-- Description:    SOFTMAX function
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[tvfSoftmax] 
            (@Data        dbo.uttListFloat READONLY
            ,@T float = 1)
RETURNS     @DataRes    TABLE 
            ([i] int
            ,[z] float) 
WITH EXECUTE AS CALLER 
AS
BEGIN
 
    IF @T = 0 
        SET @T = 1;
 
    DECLARE @Sum float = 0;
 
    SELECT       @Sum += EXP(val / @T)
        FROM     @Data;
 
    INSERT INTO  @DataRes
        SELECT   [i]
                ,EXP(val / @T) / @Sum
            FROM @Data
 
    RETURN;
END
GO

Examples

Starting with the basics.

-- MSSQLTips (T-SQL)
 
DECLARE @Table dbo.uttListFloat;
 
INSERT INTO  @Table
    VALUES   (1,1.0)
            ,(2,2.0)
            ,(3,3.0)
            ,(4,4.0)
            ,(5,3.0);
 
SELECT       T.i 
            ,CONVERT(varchar(20),T.val) AS val
            ,A.z AS zTlowSharpen
            ,B.z AS zNormal
            ,C.z AS zThighFlatten
    FROM     @Table T LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,0.5) A ON A.i = T.i LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,1.0) B ON A.i = B.i LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,5.0) C ON C.i = B.i
 
UNION ALL
 
SELECT       NULL AS i
            ,'total' AS val
            ,SUM(A.z) AS zTlowSharpen
            ,SUM(b.z) AS zTlowSharpen
            ,SUM(c.z) AS zTlowSharpen
    FROM     @Table T LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,0.5) A ON A.i = T.i LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,1.0) B ON A.i = B.i LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,5.0) C ON C.i = B.i;
GO

Resulting in:

Observe the number 4 when using the temperature of 0.5 will be sharper than the others and using higher temperatures will flatten the z outputs.

Students choosing which game to play

Suppose that a class is voting in which game they want to play and they can vote in more than one, and their preferences is:

Applying Softmax

-- MSSQLTips (T-SQL)
 
DECLARE @Table dbo.uttListFloat;
 
INSERT INTO  @Table
    VALUES   (1,8.0)
            ,(2,5.0)
            ,(3,2.0);
 
SELECT       T.i 
            ,CONVERT(varchar(20),T.val) AS val
            ,z
    FROM     @Table T LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,1.0) A ON A.i = T.i
 
UNION ALL
 
SELECT       NULL AS i
            ,'total' AS val
            ,SUM(A.z) AS zTlowSharpen
    FROM     @Table T LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,1.0) A ON A.i = T.i;
GO

Resulting in the conversion of the scores into percentages of overall preference, which show in a fair probability vote and make it clear which option is most preferred.

Prediction of text

Supposed that a user is typing “I would like a cup of…” and we are working in a predictive text system in a phone app showing the next word that possibly will fit. The Machine Learning (ML) model will try to predict based on patterns it has learned from a lot of text, but does not mean probability yet, just raw scores, like 3.0 for coffee, 2.0 for tea, and 0.5 for water.

-- MSSQLTips (T-SQL)
 
DECLARE @Table dbo.uttListFloat;
 
INSERT INTO  @Table
    VALUES   (1,3.0)
            ,(2,2.0)
            ,(3,0.5);
 
SELECT       T.i 
            ,CONVERT(varchar(20),T.val) AS val
            ,z
    FROM     @Table T LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,1.0) A ON A.i = T.i
 
UNION ALL
 
SELECT       NULL AS i
            ,'total' AS val
            ,SUM(A.z) AS zTlowSharpen
    FROM     @Table T LEFT OUTER JOIN 
             [dbo].[tvfSoftmax] (@Table,1.0) A ON A.i = T.i;
GO

Resulting in probability of 69% to be “coffee” as the next word.

In summary, ML looks at the context and assigns confidence scores to possible next words, then Softmax turns these scores into probabilities, and then the model picks words based on this probability in a way that makes sense to autocomplete in your phone application.

Next Steps

• WIKIPEDIA – Softmax function

Sebastiao Pereira

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