Earlier this year at SQL Saturday Austin 2025, Conor Cunningham gave a keynote that discussed the engineering efforts in the Austin office around SQL Server. One of the things he mentioned was PRODUCT(), which was written there and added to SQL Server 2025 to help with the GDP calculation for the US government.
Yep, that's right, a function was added since SQL Server is used in this calculation. I'm sure other customers will use this, but I decided to take a look at this function.
With a new version of SQL Server coming, I wanted to cover some of the T-SQL code changes. This is part of a series on how the T-SQL language is evolving in this version.
Note: some of these changes are already available in the various Azure SQL products.
PRODUCT()
ThePRODUCT() function is similar to SUM() in that it is an aggregate that performs a mathematical calculation. SUM() works like this. Let's assume I have this table, called dbo.Numbers:
CREATE TABLE dbo.Numbers( n INT);GOINSERT dbo.Numbers( n)VALUES(1 ),(2),(3),(4);
If I run the query below, I get this calculation: 1 + 2 + 3 +4 = 10
SELECT SUM(n) from dbo.Numbers
Most of us know this.
In SQL Server 2025, I have a PRODUCT() function that works in a similar way. In this case, if I replace SUM with PRODUCT, I get a result of 24 (1 * 2 * 3 * 4).
Easy, but why do this?
A Useful Example
There are lots of financial calculations that might want to implement a product-type function. As an example, suppose that I want to calculate the value of something based on inflation. Suppose I have inflation rate for a country each year and I'm curious what the effect is on their currency over time.
I have a table and some data from the UK, which is where Redgate is headquartered.
CREATE TABLE dbo.Inflation( Country CHAR(3), TrackedYear INT, InflationRate NUMERIC(4, 2))GOINSERT dbo.Inflation( Country, TrackedYear, InflationRate)VALUES('UK', 2019, 1.8),('UK', 2020, 0.9),('UK', 2021, 2.6),('UK', 2022, 9.1),('UK', 2023, 6.8),('UK', 2024, 3.8);Suppose I want to know how the yen has changed in value since 2019? Before product, what I would do is combine a few functions like this:
SELECT EXP(SUM(LOG(1 + (inflationRate / 100.0))))FROM dbo.InflationWHERE country = 'UK';
This gives me a result.
However, with PRODUCT, this is simpler.
SELECT PRODUCT(1 + (InflationRate / 100.0))FROM dbo.InflationWHERE Country = 'UK';
I get a similar result due to rounding, but I'd argue this is much easier to read.
PRODUCT Syntax and Usage
The syntax for this function is fairly simple. the PRODUCT() function takes a single parameter, which is the expression that is used. You can optionally include the ALL and DISTICT keywords. The official syntax diagram is:
PRODUCT( [ALL | DISTINCT] expression)
Or, if you use this as a window function:
PRODUCT ( [ ALL] expression) OVER ([PARTITION BY clause] ORDER BY clause)
Above we have seen the basic queries that use PRODUCT. What about the ALL/Distinct? Let's look at these.
ALL and DISTINCT
First, ALL. I'll add a NULL value and then run Product() with and without the ALL keywork. No difference here, as ALL is the default.
The Distinct keyword is the optional part. If I change ALL to DISTINCT, and add a duplicate "4" value, I get this.
Notice that the first value (which is using ALL by default) returns 96. This is 1 * 2 * 3 * 4 * 4 with the NULL ignored. The second is distinct with the second "4" being ignored with the NULL. Good if you really want distinct values in your calculation. Bad if you were doing something like the inflation calculation above, where there can be the duplicate values across years.
The Window Version
We can also use PRODUCT as a Window function. First, let me add some data to my Inflation table. I'll add a few other countries.
INSERT dbo.Inflation( Country, TrackedYear, InflationRate)VALUES('USA', 2019, 2.3),('USA', 2020, 1.4),('USA', 2021, 7.0),('USA', 2022, 6.5),('USA', 2023, 3.4),('USA', 2024, 2.9);GOINSERT dbo.Inflation( Country, TrackedYear, InflationRate)VALUES('AUS', 2019, 1.6),('AUS', 2020, 0.8),('AUS', 2021, 2.9),('AUS', 2022, 6.6),('AUS', 2023, 5.6),('AUS', 2024, 3.2);Now, let's call PRODUCT with the OVER() clause. If I use nothing in there, I get this:
This is interesting as the docs say ORDER BY is required. I've submitted a PR to correct this as it clearly isn't required. If I add an ORDER BY, I get values in the order, though calculation changes with the default framing. I'm not sure these results really mean anything now.
To really have useful values, I'd want a PARTITION BY since this data is really only useful by country. I'll also add the year to the results so we can see what is happening. Now I can compare some values to see what is happening by country.
We can see the US currency seems to have lost more purchasing than AUS, but less than the UK.
This is with the default framing, though it doesn't seem to matter with this function. The values are the same with ROWS and RANGE framing for this calculation.
I would assume rows and range matter here as they do for other functions. I haven't investigated extensively, but merely wanted to test if they were allowed as they aren't listed in the documentation.
Return Types
One of the interesting things about this function is that values can grow quickly. That can me large return types. There isa table that lists the data types returned based on the expression. Most of these make sense, with a larger type being returned than is passed in. The exception is INT. If an INT is in the expression, then the return type is an INT. I'd have expected a BIGINT here.
I've reproduced the table in summary form here.
- INT returned if txpression is tinyint, smallint, or int
- BIGINT returned if the expression is BIGINT
- decimal(38,0) returned if the expression is decimal (or numeric) with scale == 0
- decimal(38,6) if expression is decimal or numeric with scale != 0
- money if smallmoney or money used
- float if the expression is float
You shouldn't be using money or float/real in general, but legacy code is legacy code.
In any case, I expect some calcuations might cause overflows, though I don't know how to fix them if you get one. Maybe break the calculation into two parts? Maybe cast the expression to bigint or decimal(38,6) and hope for the best.
Summary
The PRODUCT() function isn't something I've ever wanted to use, but I do know that there are lots of calculations where this can work well. Especially in financial systems, this is likely to reduce code complexity and make it easy to read queries. This also might prevent some issues when nesting multiple functions that developers or report writers don't quite understand.
If you are involved in this type of work, take a look at the PRODUCT() function and see how it
