{"id":107277,"date":"2025-07-21T13:00:00","date_gmt":"2025-07-21T13:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107277"},"modified":"2026-04-08T10:26:38","modified_gmt":"2026-04-08T10:26:38","slug":"the-new-product-function-in-sql-server-2025","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/featured\/the-new-product-function-in-sql-server-2025\/","title":{"rendered":"SQL Server 2025 PRODUCT() Function: Multiply Values in T-SQL"},"content":{"rendered":"\n

The PRODUCT()<\/a> function, introduced in SQL Server 2025<\/a> CTP 1.3, is a new aggregate function<\/a> that multiplies values across rows – the multiplicative equivalent of SUM()<\/a>. Use it to calculate compound interest rates, cumulative growth factors, running products, and any scenario requiring set-based multiplication. <\/p>\n\n\n\n

PRODUCT() supports both standard aggregation (GROUP BY) and window function syntax (OVER clause with PARTITION BY and ORDER BY). It returns an INTEGER by default, so be aware of overflow on large results – values exceeding BIGINT range will throw an error. Before SQL Server 2025, the workaround involved logarithmic math: EXP(SUM(LOG(value))).<\/p>\n\n\n\n

Calculating a Product Without PRODUCT()<\/h2>\n\n\n\n

With each version of SQL Server<\/a>, there are always a few new features introduced that we applaud as we finally have access to a useful function that is already available elsewhere.<\/p>\n\n\n\n

Introduced in SQL Server 2025<\/a> CTP 1.3, the PRODUCT()<\/em> function<\/a> acts similarly to SUM()<\/a>,<\/em> but multiplies values rather than adds them. It is an aggregate function<\/a> in SQL Server and therefore operates on a data set, rather than on scalar values.<\/p>\n\n\n\n

Prior to the existence of this function, writing T-SQL<\/a> to multiply a series of set-based values was possible, though not exactly pretty. Consider a scenario where there is a need to multiply a set of values over time to calculate an ever-increasing multiplicative metric, such as interest or inflation.<\/p>\n\n\n\n

Consider the following data set that models a bank account where the interest rate varies over time:<\/p>\n\n\n\n

CREATE TABLE #BankAccount\n(\tRowId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,\n\tStartDate DATE NOT NULL,\n\tEndDate DATE NOT NULL,\n\tStartBalance DECIMAL(18,2) NOT NULL,\n\tEndBalance DECIMAL(18,2) NOT NULL,\n\tAnnualInterestRate DECIMAL(7,4) NOT NULL);\n\nINSERT INTO #BankAccount\n\t(StartDate, EndDate, StartBalance, EndBalance, AnnualInterestRate)\nVALUES\n\t('1\/1\/2024', '1\/31\/2024', 100.00, 100.44, 0.0523),\n\t('2\/1\/2024', '2\/29\/2024', 100.44, 100.86, 0.0501),\n\t('3\/1\/2024', '3\/31\/2024', 100.86, 101.27, 0.0490),\n\t('4\/1\/2024', '4\/30\/2024', 101.27, 101.66, 0.0463),\n\t('5\/1\/2024', '5\/31\/2024', 101.66, 102.02, 0.0424),\n\t('6\/1\/2024', '6\/30\/2024', 102.02, 102.38, 0.0426),\n\t('7\/1\/2024', '7\/31\/2024', 102.38, 102.74, 0.0407),\n\t('8\/1\/2024', '8\/31\/2024', 102.74, 103.09, 0.0395),\n\t('9\/1\/2024', '9\/30\/2024', 103.09, 103.43, 0.0382),\n\t('10\/1\/2024', '10\/31\/2024', 103.43, 103.76, 0.0381),\n\t('11\/1\/2024', '11\/30\/2024', 103.76, 104.08, 0.0374),\n\t('12\/1\/2024', '12\/31\/2024', 104.08, 104.37, 0.0329),\n\t('1\/1\/2025', '1\/31\/2025', 104.37, 104.63, 0.0300),\n        ('2\/1\/2025', '2\/28\/2025', 104.63, 104.89, 0.0299),\n        ('3\/1\/2025', '3\/31\/2025', 104.89, 105.14, 0.0283);<\/pre><\/div>\n\n\n\n
The data includes an annual interest rate that is updated monthly, which is common for most bank accounts. Calculating the end balance requires dividing the annual interest rate by twelve to get the monthly interest rate, adding one, and multiplying by the balance. For example, for January 2024 (rounded to the nearest hundredth):<\/p>\n\n\n\n
$100.00 * (1 + (0.0523 \/ 12.0000)) = $100.00 * (1 + 0.004358) = 100 * 1.004358 = 100.44<\/strong><\/em><\/p>\n\n\n\n
A useful calculation would be to determine the overall interest rate for any period of time. The most accurate way to do this would be to multiply the interest rates over the time span that is to be analyzed. For example, consider calculating the overall annual interest rate for 2024. To do this would require multiplying all twelve monthly interest rates together from 2024. There is no function to do this, though. That leaves us with two options: The iterative approach and the head-scratching math approach.<\/p>\n\n\n\n
This is a simple example of how to iterate through the table and get the annual interest rate for 2024:<\/p>\n\n\n\n
DECLARE @CurrentDate DATE = '1\/1\/2024';\nDECLARE @InterestRate DECIMAL(7,4) = 1;\nWHILE @CurrentDate <= '12\/31\/2024'\nBEGIN\n\tSELECT\n\t\t@InterestRate = @InterestRate * (1 + AnnualInterestRate \/ 12.0000)\n\tFROM #BankAccount\n\tWHERE StartDate = @CurrentDate;\n\n\tSELECT @CurrentDate = DATEADD(MONTH, 1, @CurrentDate);\nEND\nSELECT @InterestRate AS AnnualInterestRate2024;<\/pre><\/div>\n\n\n\n
The result is as follows:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
While we get the correct answer, iteration is not an ideal solution. For a large table<\/a> with thousands or millions of rows, this will quickly become slow and inefficient. When working with set-based data, the ideal solution will be set-based.<\/p>\n\n\n\n
The set-based method of solving this problem involves some mathematical manipulation. Compute the log (base ten) for each value, sum the results, and then raise the result to the power of 10. That calculation takes advantage of the Product Rule<\/em>, which is a mathematical property of logarithms. It is defined as:<\/p>\n\n\n\n
                                                           Log (X * Y) = Log X + Log Y<\/p>\n\n\n\n
Therefore, the annual interest rate for 2024 can be calculated like this:<\/p>\n\n\n\n
SELECT\n\tPOWER(10.0000, SUM(LOG10(1 + AnnualInterestRate \/ 12.0000))) AS AnnualInterestRate2024\nFROM #BankAccount\nWHERE StartDate >= '1\/1\/2024'\nAND StartDate <= '12\/1\/2024';\n<\/pre><\/div>\n\n\n\n
The result returned is:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The result is slightly different as the iterative approach rounded the result after each iteration, whereas the set-based approach rounds after all other calculations are complete. The same calculation can be made using this query, as well:<\/p>\n\n\n\n
SELECT\n\tEXP(SUM(LOG(1 + AnnualInterestRate \/ 12.0000))) AS AnnualInterestRate2024\nFROM #BankAccount\nWHERE StartDate >= '1\/1\/2024'\nAND StartDate <= '12\/1\/2024';\n<\/pre><\/div>\n\n\n\n
The only difference is that the result is not rounded:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
While these calculations are far more efficient, it is also quite confusing. Anyone reading this code will not know why logarithms and powers are used here. Even with some added documentation, most software developers will not be familiar with the mathematical properties of logarithms, nor would they be expected to.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Simplifying Code With PRODUCT()<\/h2>\n\n\n\n
SQL Server 2025 provides the long-awaited solution to this problem: The PRODUCT()<\/em> function<\/a>. This function behaves exactly like aggregate function SUM(),<\/em> except that it multiplies values rather than adding them.<\/p>\n\n\n\n
Here is the solution to the previous problem, now using PRODUCT():<\/em><\/p>\n\n\n\n
SELECT\n\tPRODUCT(1 + AnnualInterestRate \/ 12.0000) AS AnnualInterestRate2024\nFROM #BankAccount\nWHERE StartDate >= '1\/1\/2024'\n\nAND StartDate <= '12\/1\/2024';\n<\/pre><\/div>\n\n\n\n
The result is also a bit different, due to slightly different rounding:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Of all the syntax options provided so far, this is by far the simplest, easiest to read, and most efficient. The PRODUCT()<\/em> function turns longer and more complex code into a single easy-to-read function call. This function is optimized for batch mode operations and will result in significant performance gains when batch mode is used, regardless of whether on columnstore or rowstore indexes.<\/p>\n\n\n\n
Details of PRODUCT()<\/h2>\n\n\n\n
The PRODUCT()<\/em> function has some details that are worth noting here that can affect how we use it or the results that are returned. These are some of the more common questions that could be asked about it.<\/p>\n\n\n\n
INT vs. BIGINT Results<\/h3>\n\n\n\n
First, this function returns an INTEGER<\/a> data type. This is important as multiplying many numbers together can quickly result in a very large number. If the number exceeds the threshold of an integer in either the positive or negative direction, then an error will be returned. The limits for each integer data type are provided here<\/a>, for reference.<\/p>\n\n\n\n
While 2.1 billion may seem like a big number, it is quite easy to exceed it when talking about world population, data size, money, or other biggie-sized data points. A simple example of what happens when the result exceeds this limit is as follows:<\/p>\n\n\n\n
SELECT\n\tPRODUCT(TaxRate)\nFROM Sales.OrderLines;\n<\/pre><\/div>\n\n\n\n
That query multiplies a larger set of numbers and ultimately breaks the INTEGER limit. The result is an error message:<\/p>\n\n\n\n
Msg 8115, Level 16, State 2, Line 68\nArithmetic overflow error converting expression to data type numeric.\n<\/pre><\/div>\n\n\n\n
Since there is no BIGINT version of PRODUCT()<\/em> (yet), it is important to be aware of data size and ensure that results can\u2019t throw an error like above. COUNT()<\/a><\/em> is the only aggregate function that currently has a BIGINT version available, COUNT_BIG().<\/em> Therefore, we should treat PRODUCT()<\/em> with the same caution that we would treat SUM().<\/em><\/p>\n\n\n\n
NULL Handling<\/h3>\n\n\n\n
NULL<\/a> is handled the same way as it is for other aggregate functions. This can be easily tested by adding NULL to the test data set from above:<\/p>\n\n\n\n
ALTER TABLE #BankAccount ALTER COLUMN AnnualInterestRate DECIMAL(7,4) NULL;\n\nINSERT INTO #BankAccount\n\t(StartDate, EndDate, StartBalance, EndBalance, AnnualInterestRate)\nVALUES\n\t('4\/1\/2025', '4\/30\/2025', 105.14, 105.14, NULL),\n('5\/1\/2025', '5\/31\/2025', 105.14, 105.14, NULL),\n('6\/1\/2025', '6\/30\/2025', 105.14, 105.14, NULL);\n<\/pre><\/div>\n\n\n\n
Calculating the annual interest rate for 2025 can be done with this (slightly adjusted) query:<\/p>\n\n\n\n
SELECT\n\tPRODUCT(1 + AnnualInterestRate \/ 12.0000) AS AnnualInterestRate2025\nFROM #BankAccount\nWHERE StartDate >= '1\/1\/2025'\n\nAND StartDate <= '12\/1\/2025';\n<\/pre><\/div>\n\n\n\n
The result is calculated using the values for January-March:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
NULL is ignored in the calculation and only the previously populated values are taken into consideration.<\/p>\n\n\n\n
PRODUCT() As a Window Function<\/h3>\n\n\n\n
The PRODUCT()<\/em> function can be used as part of a window function<\/a> to compute a product across a non-aggregated data set. This can be a useful way to add in more complex metrics, such as a running multiplier.<\/p>\n\n\n\n
The following example calculates a running interest rate per calendar year:<\/p>\n\n\n\n
SELECT\n\tStartDate,\n\tDATEPART(YEAR, StartDate) AS CurrentYear,\n\tAnnualInterestRate,\n\tPRODUCT(1 + AnnualInterestRate \/ 12.0000) OVER (PARTITION BY DATEPART(YEAR, StartDate) ORDER BY StartDate\n\tROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningAnnualTotal\nFROM #BankAccount\n\nORDER BY StartDate;\n<\/pre><\/div>\n\n\n\n
The results show a running multiplier for interest rate that resets at the start of a new year:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Note that when the interest rate is NULL for 4\/1\/2025-6\/1\/2025, the running total is still correctly reported as PRODUCT<\/em> ignores NULL and simply returns the running total up through the actual values provided previously.<\/p>\n\n\n\n
A similar query can be crafted to return a running total by quarter:<\/p>\n\n\n\n
SELECT\n\tStartDate,\n\tDATEPART(QUARTER, StartDate) AS CurrentQuarter,\n\tAnnualInterestRate,\n\tPRODUCT(1 + AnnualInterestRate \/ 12.0000) OVER (PARTITION BY DATEPART(YEAR,\nStartDate), DATEPART(QUARTER, StartDate) ORDER BY StartDate\n\tROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningQuarterlyTotal\nFROM #BankAccount\n\nORDER BY StartDate;\n<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
As expected, the interest rate accumulates for each of the three months per quarter and resets when the next quarter starts. 2025 Quarter 2 only shows NULL as all interest rates are reported as NULL. If PRODUCT()<\/em> receives no non-NULL values, then NULL is returned.
Read also: <\/strong>CHOOSE function for value mapping in T-SQL<\/a><\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
PRODUCT()<\/em> provides functionality that will make lives easier for many data engineers and analysts. Being able to calculate a running product quickly and efficiently with simple syntax is long-awaited functionality for SQL Server!<\/p>\n\n\n\n
This is especially useful for percentages and rates, where long-term calculations can become cumbersome and inefficient. Take note of large numbers, though, as PRODUCT()<\/em> is only designed to return an INTEGER. Results that cross into BIGINT territory will throw an error instead of the expected result.
Read also:<\/strong> AI embeddings in SQL Server 2025<\/a><\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Simple Talk is brought to you by Redgate Software<\/h3>\n                
\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                            Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: The new PRODUCT() function in SQL Server 2025<\/h2>\n\n                        
1. What does the PRODUCT() function do in SQL Server 2025?<\/h3>\n            
\n                
PRODUCT() is an aggregate function that multiplies all non-NULL values in a column, similar to how SUM() adds them. For example, PRODUCT(Rate) across rows containing 1.05, 1.03, and 1.02 returns 1.103 (the cumulative product). It supports GROUP BY for grouped results and the OVER clause for running products via window functions.<\/p>\n            <\/div>\n                    
2. How did you multiply values across rows before SQL Server 2025?<\/h3>\n            
\n                
The standard workaround uses logarithms: EXP(SUM(LOG(value))). This works because LOG converts multiplication into addition, SUM adds the logarithms, and EXP converts back. The limitation is that LOG requires positive values and the result can have floating-point precision loss. The new PRODUCT() function is cleaner, more readable, and handles NULL values natively.<\/p>\n            <\/div>\n                    
3. Can you use PRODUCT() as a window function in SQL Server 2025?<\/h3>\n            
\n                
Yes. PRODUCT() supports the OVER clause with PARTITION BY and ORDER BY, plus frame specifications like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This makes it straightforward to calculate running products – for example, cumulative interest rates per quarter – without self-joins or cursors.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn the new PRODUCT() aggregate function in SQL Server 2025 – multiply values across rows like SUM() adds them. Covers syntax, window function usage, workarounds for earlier versions, and integer overflow behavior.…<\/p>\n","protected":false},"author":329827,"featured_media":107292,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143524],"tags":[4170,4150,4151,159254,159319,4252],"coauthors":[101655],"class_list":["post-107277","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-sql-server","tag-database-administration","tag-sql","tag-sql-server","tag-sql-server-2025","tag-sqlserver2025publicpreview","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107277","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/329827"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=107277"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107277\/revisions"}],"predecessor-version":[{"id":109272,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107277\/revisions\/109272"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107292"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107277"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}