Problem
The Newton-Cotes formulas are a useful and straightforward family of numerical integration, which is the approximate computation of an integral using numerical techniques. The numerical computation of an integral is also called quadrature. How can we build these formulas in SQL Server without using external tools?
Solution
Numerical integration is the bridge between theoretical math and the reality of the physical world. It is used in physics, engineering, probability and statistics, navigation, robotics, computer graphics, games, and others.
These formulas are based on the work of Isaac Newton and Roger Cotes.
Newton-Cotes formulas Explained
There are two types of Newton-Cotes formulas:
- Closed: use both endpoints’ values and also interior points. The endpoints of the interval and the function at these points are kwon and well-behaved once they are smooth.
- Open: this function uses only interior points, excluding the endpoints which are problematic.
The Newton-Cotes formulas using n + 1 points can be defined as:
is approximately equal to 
where:
- For a closed formula:
with 
- For an open formula:
with 
The number h is called step size and 
are called weights, which can be computed as the integral of Lagrange basis polynomials and depend only on 
and not on the function f. I will not enter in details about the calculations and I am going to use the formulas direct. You can find the details in the links at the end in the Next Steps.
There is also a point to call attention that is the called Runge’s phenomenon where the errors grow exponentially for a large n.
It is best used in cases of one dimension problem, smooth functions, moderate accuracy, cheap evaluations, and for educational or demo purposes.
Newton-Cotes formulas are the first systematic quadrature methods in mathematics, which provides a clear view between theory and computation. Newton-Cotes are also an excellent case study of numerical instability and to exemplify Runge’s phenomenon and remains relevant as lightweight computation.
SQL Functions
Newton-Cotes Math
The first function we are going to store is the mathematical function that will be used. Just change this one to the math function you wish to use.
-- ==================================================
-- Author: SCP
-- Create date: 20260219
-- Description: Newton-Cotes Math formula
-- ==================================================
CREATE OR ALTER FUNCTION [dbo].[ufnNewtonCotesMath]
(@x float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN SIN(@x);
END
GONewton-Cotes Closed
-- ==================================================
-- Author: SCP
-- Create date: 20260219
-- Description: Newton-Cotes formulas - Closed type
-- ==================================================
CREATE OR ALTER FUNCTION [dbo].[ufnNewtonCotesClosed]
(@n int
,@a float
,@b float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @h float = (@b - @a) / @n;
IF @n = 1 -- TRAPEZOIDAL RULE ==========================
RETURN @h * @n / 2.0 *
([dbo].[ufnNewtonCotesMath] (@a) +
[dbo].[ufnNewtonCotesMath] (@b));
ELSE IF @n = 2 -- SIMPSON´S RULE ============================
RETURN @h * @n / 6.0 *
([dbo].[ufnNewtonCotesMath] (@a) +
[dbo].[ufnNewtonCotesMath] (@a + @h) * 4.0 +
[dbo].[ufnNewtonCotesMath] (@b));
ELSE IF @n = 3 -- SIMPSON´S 3/8 RULE ========================
RETURN @h * @n / 8.0 *
([dbo].[ufnNewtonCotesMath] (@a) +
[dbo].[ufnNewtonCotesMath] (@a + @h) * 3.0 +
[dbo].[ufnNewtonCotesMath] (@a + 2.0 * @h) * 3.0 +
[dbo].[ufnNewtonCotesMath] (@b));
ELSE IF @n = 4 -- BOOLE´S RULE ==============================
RETURN @h * @n / 90.0 *
([dbo].[ufnNewtonCotesMath] (@a) * 7.0 +
[dbo].[ufnNewtonCotesMath] (@a + @h) * 32.0 +
[dbo].[ufnNewtonCotesMath] (@a + 2.0 * @h) * 12.0 +
[dbo].[ufnNewtonCotesMath] (@a + 3.0 * @h) * 32.0 +
[dbo].[ufnNewtonCotesMath] (@b) * 7.0);
ELSE IF @n = 5 -- ===========================================
RETURN @h * @n / 288.0 *
([dbo].[ufnNewtonCotesMath] (@a) * 19.0 +
[dbo].[ufnNewtonCotesMath] (@a + @h) * 75.0 +
[dbo].[ufnNewtonCotesMath] (@a + 2.0 * @h) * 50.0 +
[dbo].[ufnNewtonCotesMath] (@a + 3.0 * @h) * 50.0 +
[dbo].[ufnNewtonCotesMath] (@a + 4.0 * @h) * 75.0 +
[dbo].[ufnNewtonCotesMath] (@b) * 19.0);
ELSE IF @n = 6 -- WEDDLE RULE ===============================
RETURN @h * @n / 840.0 *
([dbo].[ufnNewtonCotesMath] (@a) * 41.0 +
[dbo].[ufnNewtonCotesMath] (@a + @h) * 216.0 +
[dbo].[ufnNewtonCotesMath] (@a + 2.0 * @h) * 27.0 +
[dbo].[ufnNewtonCotesMath] (@a + 3.0 * @h) * 272.0 +
[dbo].[ufnNewtonCotesMath] (@a + 4.0 * @h) * 27.0 +
[dbo].[ufnNewtonCotesMath] (@a + 5.0 * @h) * 216.0 +
[dbo].[ufnNewtonCotesMath] (@b) * 41.0);
RETURN NULL;
END
GONewton-Cotes Open
-- ==================================================
-- Author: SCP
-- Create date: 20260224
-- Description: Newton-Cotes formulas - Open type
-- ==================================================
CREATE OR ALTER FUNCTION [dbo].[ufnNewtonCotesOpen]
(@n int
,@a float
,@b float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @h float = (@b - @a) / (@n + 2);
IF @n = 0 -- MIDPOINT RULE =============================
RETURN @h * (@n + 2) *
([dbo].[ufnNewtonCotesMath] (@a + @h));
ELSE IF @n = 1 -- ===========================================
RETURN @h * (@n + 2) / 2.0 *
([dbo].[ufnNewtonCotesMath] (@a + @h) +
[dbo].[ufnNewtonCotesMath] (@a + 2 * @h));
ELSE IF @n = 2 -- ===========================================
RETURN @h * (@n + 2) / 3.0 *
([dbo].[ufnNewtonCotesMath] (@a + @h) * 2 -
[dbo].[ufnNewtonCotesMath] (@a + 2 * @h) +
[dbo].[ufnNewtonCotesMath] (@a + 3 * @h) * 2);
ELSE IF @n = 3 -- ===========================================
RETURN @h * (@n + 2) / 24.0 *
([dbo].[ufnNewtonCotesMath] (@a + @h) * 11 +
[dbo].[ufnNewtonCotesMath] (@a + 2 * @h) +
[dbo].[ufnNewtonCotesMath] (@a + 3 * @h) +
[dbo].[ufnNewtonCotesMath] (@a + 4 * @h) * 11);
ELSE IF @n = 4 -- ===========================================
RETURN @h * (@n + 2) / 20.0 *
([dbo].[ufnNewtonCotesMath] (@a + @h) * 11 -
[dbo].[ufnNewtonCotesMath] (@a + 2 * @h) * 14 +
[dbo].[ufnNewtonCotesMath] (@a + 3 * @h) * 26 -
[dbo].[ufnNewtonCotesMath] (@a + 4 * @h) * 14 +
[dbo].[ufnNewtonCotesMath] (@a + 5 * @h) * 11);
ELSE IF @n = 5 -- WEDDLE RULE ===============================
RETURN @h * (@n + 2) / 1440.0 *
([dbo].[ufnNewtonCotesMath] (@a + @h) * 611 -
[dbo].[ufnNewtonCotesMath] (@a + 2 * @h) * 453 +
[dbo].[ufnNewtonCotesMath] (@a + 3 * @h) * 562 +
[dbo].[ufnNewtonCotesMath] (@a + 4 * @h) * 562 -
[dbo].[ufnNewtonCotesMath] (@a + 5 * @h) * 453 +
[dbo].[ufnNewtonCotesMath] (@a + 6 * @h) * 611);
RETURN NULL;
END
GOExample 1
The equation in the function Newton-Cotes math I am using is f(x) = sin(x) and let’s define the values for a = 0 and b = 2 and the expected area under the curve should be 1.4161468365471424
-- MsSqlTips (TSQL)
SELECT 'Closed' NewtonCotes
,[dbo].[ufnNewtonCotesClosed] (1,0,2) AS n1
,[dbo].[ufnNewtonCotesClosed] (2,0,2) AS n2
,[dbo].[ufnNewtonCotesClosed] (3,0,2) AS n3
,[dbo].[ufnNewtonCotesClosed] (4,0,2) AS n4
,[dbo].[ufnNewtonCotesClosed] (5,0,2) AS n5
,[dbo].[ufnNewtonCotesClosed] (6,0,2) AS n6;
SELECT 'Open' NewtonCotes
,[dbo].[ufnNewtonCotesOpen] (0,0,2) AS n0
,[dbo].[ufnNewtonCotesOpen] (1,0,2) AS n1
,[dbo].[ufnNewtonCotesOpen] (2,0,2) AS n2
,[dbo].[ufnNewtonCotesOpen] (3,0,2) AS n3
,[dbo].[ufnNewtonCotesOpen] (4,0,2) AS n4
,[dbo].[ufnNewtonCotesOpen] (5,0,2) AS n5;
-- Solving the integral equation to obtain the expected value
SELECT COS(0) - COS(2) AS ExpectedValue;
GOResulting in
As you can observe the values converged when n increases with a high accuracy for n > 3 for the function sin(x).
Example 2
Let´s change our math function to 
-- ==================================================
-- Author: SCP
-- Create date: 20260219
-- Description: Newton-Cotes Math formula
-- ==================================================
CREATE OR ALTER FUNCTION [dbo].[ufnNewtonCotesMath]
(@x float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN EXP(-0.1 * @x);
END
GOAnd now to the calculation for the range 0 to 10
-- MsSqlTips (TSQL)
SELECT 'Closed' NewtonCotes
,[dbo].[ufnNewtonCotesClosed] (1,0,10) AS n1
,[dbo].[ufnNewtonCotesClosed] (2,0,10) AS n2
,[dbo].[ufnNewtonCotesClosed] (3,0,10) AS n3
,[dbo].[ufnNewtonCotesClosed] (4,0,10) AS n4
,[dbo].[ufnNewtonCotesClosed] (5,0,10) AS n5
,[dbo].[ufnNewtonCotesClosed] (6,0,10) AS n6;
SELECT 'Open' NewtonCotes
,[dbo].[ufnNewtonCotesOpen] (0,0,10) AS n0
,[dbo].[ufnNewtonCotesOpen] (1,0,10) AS n1
,[dbo].[ufnNewtonCotesOpen] (2,0,10) AS n2
,[dbo].[ufnNewtonCotesOpen] (3,0,10) AS n3
,[dbo].[ufnNewtonCotesOpen] (4,0,10) AS n4
,[dbo].[ufnNewtonCotesOpen] (5,0,10) AS n5;
SELECT 10 * (1 - EXP(-1)) AS ExpectedValue;
GOResulting in
Next Steps
Sometimes, in numerical integration it is necessary to apply composite quadrature rules when a single math formula on the whole interval is not accurate or stable enough and then instead of have a one big polynomial, we are going to have many small polynomials which is better numerically. Normally it is used the composite rules when the difference between a and b value is big and a single polynomial will bend too much.
- WIKIPEDIA – Newton–Cotes formulas
- WIKIPEDIA – Runge’s phenomenon
- Related Articles:
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
