SQL Server 2025 Product() Function Examples

Problem

SQL Server 2025 includes new features and enhancements. In the previous SQL Server 2025 tips, we have explored many new features. Have you explored the new Product() function? If not, this will walk you through the Product() function with several examples.

Solution

SQL Server 2025 introduces a new function, Product(), to return multiplier values. This function works similar to the SUM() function; the only difference is that it returns the product instead of the sum of the values.

Suppose you have a retail e-commerce application. You regularly run promotional offers for your customers, especially during the holiday season.

You have the following discounts:

Example Walkthrough

Let’s insert this data into a temp table #Discounts using the following T-SQL:

DROP TABLE IF EXISTS #Discounts
CREATE TABLE #Discounts (
    ProductID INT,
    DiscountFactor DECIMAL(5,4)
);
 
INSERT INTO #Discounts VALUES
(1, 0.9),   
(2, 0.95),  
(3, 0.99);  

Mathematically, the key logarithmic rule is:

In SQL Server 2022 or earlier, we do not have the Product() aggregate function; therefore, we can use the following mathematical formula for calculations.

If you exponentiate both sides, you reverse the logarithm:

You can write this as follows:

Let’s use this formula in T-SQL to calculate the multiplied discount factor:

SELECT ProductID,(1-EXP(SUM(LOG(DiscountFactor))))*100 AS [ProductDiscount%]
FROM #Discounts GROUP BY ProductID;

Using SQL Product() Function

In this case, we need to use the EXP, SUM, and LOG functions to calculate multiplication. The Product() function works like the aggregate function SUM().

Let’s see how this product’s function can be suitable to get the multiplied product discount.

Using the new Product() function, it is a simple and efficient way to write code, which reduces complexity.

SQL Server Product() Function Syntax

Let’s look at the Product() function syntax:

PRODUCT ( [ ALL | DISTINCT ] expression )

• All: By default, the product function aggregates all values, regardless of duplicates.
• DISTINCT: By specifying the DISTINCT clause, the product function returns the product of distinct(unique) values.

To test the distinct clause, let’s insert a few duplicate values in this table. The default Product() function definition uses all values to calculate the multiplication.

INSERT INTO #Discounts VALUES
(1, 0.9),  
(1, 0.95), 
(1, 0.9),  
(2, 0.8),
(2, 0.8),
(2, 0.9);
 
SELECT ProductID, (1-PRODUCT(DiscountFactor))*100 As WithoutDistinct FROM #Discounts GROUP BY ProductID;
 
SELECT ProductID, (1-PRODUCT(DISTINCT DiscountFactor))*100 As WithDistinct FROM #Discounts GROUP BY ProductID;;

With Distinct, it discarded the duplicate record 0.9 for multiplication for product ID 1.

0.9*0.95 = 0.85

Product Discount = 1-0.85 = 14.50%

Handling NULL values with the Product function

Let’s add a few NULL values in the data and see how the Product function treats them. Does it ignore NULL values or raise an exception?

INSERT INTO #Discounts VALUES
(1, 0.9),  
(1, 0.95), 
(1, NULL),  
(2, 0.8),
(2, 0.8),
(2, 0.9);
SELECT (1-Product(DiscountFactor))*100 AS [ProductDiscount%] FROM #Discounts GROUP BY ProductID;;

As shown above, ProductID 1 contains a NULL value. The Product function ignores the NULL value, returning 14.50% (i.e., 1 – (0.9*0.95)). It is in line with the other aggregate function, SUM().

Using Product() with OVER ( [ PARTITION BY clause ] ORDER BY clause )

We can use the Product() function with the partition by clause to partition the dataset into multiple sections based on the defined criteria. Further, the product function is applied to each partition to perform calculations.

In the following example, we have a temporary table that holds items, their transaction date, and the return on the product sale. The return can be positive (profit) or negative (loss) based on the selling price relative to the buying price. For simplicity, I just included the product’s return on this table.

DROP TABLE IF EXISTS #ProductsAnalysis
CREATE TABLE #ProductsAnalysis (
    Item Varchar(50),
    TransDate Date, 
    [Return] DECIMAL(5,2)
);
Insert into #ProductsAnalysis VALUES
    ('Product-A', '2025-01-01', 0.0500),   
    ('Product-A', '2025-02-01', -0.0200), 
    ('Product-A', '2025-03-01', 0.0400),  
    ('Product-B', '2025-01-01', 0.0300),  
    ('Product-B', '2025-02-01', 0.0600),  
    ('Product-C', '2025-01-01', 0.1000)   
 SELECT Item,
       TransDate,
       [Return]
FROM #ProductsAnalysis 

We’ll now calculate running compounded returns using Product() as a SQL window function.

SELECT Item,
       TransDate,
       [Return],
       PRODUCT(1 + [Return]) OVER (PARTITION BY Item ORDER BY TransDate 
       ROWS UNBOUNDED PRECEDING ) - 1 AS RunningCompoundedReturn,
       ((PRODUCT(1 + [Return]) OVER (PARTITION BY Item  ORDER BY TransDate 
       ROWS UNBOUNDED PRECEDING   ) - 1) * 100) AS RunningReturnPercent 
FROM #ProductsAnalysis 
ORDER BY Item, TransDate;

Let’s understand the above code first:

• PARTITION BY Item: It partitions the data based on the item list, such as Product-A and Product-B.
• Order by: The data is sorted by the transaction date.
• ROW UNBOUND PROCEEDING: It is to include all rows from the start up to the current one which is a running total.
• PRODUCT(1 + Return) OVER ( PARTITION BY Item ORDER BY TransDate ROWS UNBOUNDED PRECEDING ) – 1 AS RunningCompoundedReturn: It is for running (cumulative) compounded return for each item over time. Here, -1 converts the growth multiplier to a return a percentage.

The query returned the following results:

Let’s interpret the results for Product-A. For Product-A, we have three data points available:

First Row:

• Multiplier= 1+0.5 = 1.05
• Running Product: 1.05
• Cumulative Return: (1.05-1 )*100 = 5%

Second Row:

• Multiplier= 1-0.2= 0.98
• Running Product: 1.05* 0.98 = 1.029
• Cumulative Return: (1.029-1 )*100 = 2.90%

Third Row:

• Multiplier= 1+0.4 = 1.04
• Running Product: 1.029*1.04 = 1.0701
• Cumulative Return: (1.0701-1 )*100 = 7.01%

As we can see, Product-A is the first group. Similarly, we can do calculations for the remaining groups, Product-B and Product-C.

Output data types of the Product function

The following table represents the input and output data types of the Product() function:

• Tinyint -> tinyint
• Smallint -> smallint
• Int -> int
• Bigint -> bigint
• Float -> float

The Product() function’s output depends on the input data type. If the input values of the Product function are of the Integer data type, the output will be an integer as well.

Let’s walk through a practical scenario where:

• The input values are integers
• The Product() function produces a result that exceeds INT range

drop table if exists #ProductIntRangeTest
CREATE TABLE #ProductIntRangeTest (
    ID INT,
    [Value] INT
);
INSERT INTO #ProductIntRangeTest VALUES
(1, 2000),
(2, 3000),
(3, 4000),
(4, 5000);
 
SELECT PRODUCT([Value]) AS Result
FROM #ProductIntRangeTest;

As shown below, you get an exception “Arithmetic overflow error converting expression to data type int”. It is because the product function is trying to give output as an integer data type, while the output value falls beyond the integer range, i.e., 2,147,483,647

Note: You should always consider the output data range when using the Product() function, as you might encounter exceptions if it’s not planned correctly.

To resolve this error, you can explicitly cast the output to BIGINT as shown below:

SELECT PRODUCT(CAST(Value AS BIGINT)) AS Result
FROM #ProductIntRangeTest;

Next Steps

• Go through existing SQL Server 2025 tips and be familiar with the latest release of SQL Server.
• Refer to the product function documentation for more details.

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book “DP-300 Administering Relational Database on Microsoft Azure.” I can be reached at: Rajendra.gupta16@gmail.com for any consulting help.

• MSSQLTips Awards:
• Author of the Year – 2022 | Author Contender – 2021/2023/2024 | Champion Award (100+ tips) – 2020