In SQL Server the STDEV() function returns the statistical standard deviation of all values in a specified expression.

So we can use this function to get the standard deviation of all values in a column.

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:

STDEV ( [ ALL | DISTINCT ] expression )

And as an analytical function:

STDEV ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) 

The expression must be a numeric expression.

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 STDEV() function against the Score column like this:

SELECT STDEV(Score) FROM Dogs;

Result:

33.704599092705436

The STDEV() 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 STDEV(DISTINCT Score) FROM Dogs;

Result:

36.339667425162666

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 STDEV() function treated those two as one. This resulted in a different standard deviation.

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 STDEV(ALL Score) FROM Dogs;

Result:

33.704599092705436

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 STDEV() as a window function.

Example:

SELECT 
    Activity,
    DogName,
    Score,
    STDEV(Score) OVER(ORDER BY Activity) AS "STDEV"
FROM Dogs;

Result:

Activity     DogName  Score  STDEV             
-----------  -------  -----  ------------------
Catch Cat    null     null   null              
Fetch Stick  Bruno    43     27.712812921102035
Fetch Stick  Cooper   43     27.712812921102035
Fetch Stick  Max      91     27.712812921102035
Keep Quiet   Bruno    3      36.86732971073441 
Keep Quiet   Cooper   null   36.86732971073441 
Keep Quiet   Max      1      36.86732971073441 
Wag Tail     Bruno    65     33.704599092705436
Wag Tail     Cooper   51     33.704599092705436
Wag Tail     Max      87     33.704599092705436

Here, I used an ORDER BY clause in the OVER clause to specify the logical order in which the operation is performed.

If we were to put another ORDER BY clause at the end of the query, it would 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,
    STDEV(Score) OVER(PARTITION BY Activity) AS "STDEV"
FROM Dogs;

Result:

Activity     DogName  Score  STDEV             
-----------  -------  -----  ------------------
Catch Cat    null     null   null              
Fetch Stick  Bruno    43     27.712812921102035
Fetch Stick  Cooper   43     27.712812921102035
Fetch Stick  Max      91     27.712812921102035
Keep Quiet   Bruno    3      1.4142135623730951
Keep Quiet   Cooper   null   1.4142135623730951
Keep Quiet   Max      1      1.4142135623730951
Wag Tail     Bruno    65     18.147543451754924
Wag Tail     Cooper   51     18.147543451754924
Wag Tail     Max      87     18.147543451754924

In this case we partitioned by the Activity column, and so we got the standard deviation within each activity.

Here it is with an ORDER BY clause added to the OVER clause:

SELECT 
    Activity,
    DogName,
    Score,
    STDEV(Score) OVER(PARTITION BY Activity ORDER BY Score) AS "STDEV"
FROM Dogs;

Result:

Activity     DogName  Score  STDEV             
-----------  -------  -----  ------------------
Catch Cat    null     null   null              
Fetch Stick  Bruno    43     0                 
Fetch Stick  Cooper   43     0                 
Fetch Stick  Max      91     27.712812921102035
Keep Quiet   Cooper   null   null              
Keep Quiet   Max      1      null              
Keep Quiet   Bruno    3      1.4142135623730951
Wag Tail     Cooper   51     null              
Wag Tail     Bruno    65     9.899494936611665 
Wag Tail     Max      87     18.147543451754924

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,
    STDEV(DISTINCT Score) OVER(PARTITION BY Activity ORDER BY Score) AS "STDEV"
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.