In SQL Server the VARP() function returns the statistical variance for the population for all values in the specified expression.
Syntax
The function can be used as an aggregate function or as an analytical/window function.
When used as an aggregate function, the syntax goes like this:
VARP ( [ ALL | DISTINCT ] expression )And as an analytical function:
VARP ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) The expression must be either an exact numeric or approximate numeric data type, except for the bit data type.
Example
Suppose we have a table like this:
SELECT * FROM Dogs;Result:
DogName Activity Score ------- ----------- ----- Bruno Fetch Stick 43 Cooper Fetch Stick 43 Max Fetch Stick 91 Bruno Wag Tail 65 Cooper Wag Tail 51 Max Wag Tail 87 Bruno Keep Quiet 3 Cooper Keep Quiet null Max Keep Quiet 1 null Catch Cat null
We can use the VARP() function against the Score column like this:
SELECT VARP(Score) FROM Dogs;Result:
994
The VARP() function ignores NULL values and so these aren’t factored into the results.
The DISTINCT Argument
By default, all rows are evaluated, regardless of whether they contain unique values. However, we can use the DISTINCT keyword to specify that only distinct (unique) values are evaluated:
SELECT VARP(DISTINCT Score) FROM Dogs;Result:
1131.918367346939
We can see that this has changed the result slightly. This is because our underlying data contains duplicates – two rows contain 43. When we used the DISTINCT keyword, the VARP() function treated those two as one. This produced a different result.
The ALL Argument
By default, all rows are evaluated regardless of whether they contain duplicates. In other words, this is the case when we don’t use the DISTINCT argument to eliminate duplicates from evaluation.
However, we also have the option of using the ALL keyword to explicitly specify that all values are evaluated:
SELECT VARP(ALL Score) FROM Dogs;Result:
994
We can see that this result is the same as the first example. That’s because the functionality is exactly the same when we use the ALL keyword.
The OVER Clause
We can include an OVER clause in order to use VARP() as a window function.
Example:
SELECT
Activity,
DogName,
Score,
VARP(Score) OVER(ORDER BY Activity) AS "VARP"
FROM Dogs;Result:
Activity DogName Score VARP ----------- ------- ----- ------------------ Catch Cat null null null Fetch Stick Bruno 43 512 Fetch Stick Cooper 43 512 Fetch Stick Max 91 512 Keep Quiet Bruno 3 1087.3600000000001 Keep Quiet Cooper null 1087.3600000000001 Keep Quiet Max 1 1087.3600000000001 Wag Tail Bruno 65 994 Wag Tail Cooper 51 994 Wag Tail Max 87 994
Here, I used an ORDER BY clause in the OVER clause to specify the logical order in which the operation is performed.
We could also put another ORDER BY clause at the end of the query in order to determine how the result set is sorted.
Partitioning the Results
The OVER clause also accepts a PARTITION BY clause that allows us to partition the results by a given column.
Here’s an example:
SELECT
Activity,
DogName,
Score,
VARP(Score) OVER(PARTITION BY Activity) AS "VARP"
FROM Dogs;Result:
Activity DogName Score VARP ----------- ------- ----- ------------------ Catch Cat null null null Fetch Stick Bruno 43 512 Fetch Stick Cooper 43 512 Fetch Stick Max 91 512 Keep Quiet Bruno 3 1 Keep Quiet Cooper null 1 Keep Quiet Max 1 1 Wag Tail Bruno 65 219.55555555555534 Wag Tail Cooper 51 219.55555555555534 Wag Tail Max 87 219.55555555555534
In this case we partitioned by the Activity column, and so the result was specific within each activity.
Here it is with an ORDER BY clause added to the OVER clause:
SELECT
Activity,
DogName,
Score,
VARP(Score) OVER(PARTITION BY Activity ORDER BY Score) AS "VARP"
FROM Dogs;Result:
Activity DogName Score VARP ----------- ------- ----- ------------------ Catch Cat null null null Fetch Stick Bruno 43 0 Fetch Stick Cooper 43 0 Fetch Stick Max 91 512 Keep Quiet Cooper null null Keep Quiet Max 1 0 Keep Quiet Bruno 3 1 Wag Tail Cooper 51 0 Wag Tail Bruno 65 49 Wag Tail Max 87 219.55555555555534
No DISTINCT Clause with the OVER Clause
We can’t use the DISTINCT clause when using the OVER clause.
Here’s what happens when we add the DISTINCT clause to the previous example:
SELECT
Activity,
DogName,
Score,
VARP(DISTINCT Score) OVER(PARTITION BY Activity ORDER BY Score) AS "VARP"
FROM Dogs;Result:
Msg 10759, Level 15, State 1, Line 5 Use of DISTINCT is not allowed with the OVER clause.
However, we can use the ALL clause if we want.