Updated: 24 May 2013

Use the aggregate function VAR_P to calculate the variance for an entire population. VAR_P is a numerically stable single-pass calculation of the population variance which will produce more reliable results under certain conditions than the built-in SQL Server function VARP, without compromising performance.

For a population *N* having a distribution where the mean is not known, the population variance is defined as:

the values to be used in the calculation. *@Known_x* must be of a type **float** or of a type that implicitly converts to **float**.

float

· If you want measure the variance for a sample, then use the VAR_S function.

· To calculate the population standard deviation use the STDEV_P function.

· To calculate the sample standard deviation use the STDEV_S function.

· To calculate a running population variance use the RunningVARP function.

· To calculate a moving population variance use the MovingVARP function.

SELECT wct.VAR_P(x) as VAR_P

FROM (

SELECT 30000.0000216303 UNION ALL

SELECT 30000.0000565854 UNION ALL

SELECT 30000.000038137 UNION ALL

SELECT 30000.0000495983 UNION ALL

SELECT 30000.0000185861 UNION ALL

SELECT 30000.0000863479 UNION ALL

SELECT 30000.0000776366 UNION ALL

SELECT 30000.0000637985 UNION ALL

SELECT 30000.0000939786 UNION ALL

SELECT 30000.000031191 UNION ALL

SELECT 30000.0000550457 UNION ALL

SELECT 30000.0000207558 UNION ALL

SELECT 30000.0000805531 UNION ALL

SELECT 30000.0000241287

)n(x)

This produces the following result

VAR_P

----------------------

6.42828821986598E-10

In this example, using the same data, we compare the XLeratorDB value to the value returned by the native SQL Server function.

SELECT wct.VAR_P(x) as XLeratorDB

,VARP(x) as [SQL Server]

FROM (

SELECT 30000.0000216303 UNION ALL

SELECT 30000.0000565854 UNION ALL

SELECT 30000.000038137 UNION ALL

SELECT 30000.0000495983 UNION ALL

SELECT 30000.0000185861 UNION ALL

SELECT 30000.0000863479 UNION ALL

SELECT 30000.0000776366 UNION ALL

SELECT 30000.0000637985 UNION ALL

SELECT 30000.0000939786 UNION ALL

SELECT 30000.000031191 UNION ALL

SELECT 30000.0000550457 UNION ALL

SELECT 30000.0000207558 UNION ALL

SELECT 30000.0000805531 UNION ALL

SELECT 30000.0000241287

)n(x)

This produces the following result.

XLeratorDB SQL Server

---------------------- ----------------------

6.42828821986598E-10 0