var-transact-sql.md

title	VAR (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/13/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	VAR VAR_TSQL
dev_langs	TSQL
helpviewer_keywords	statistical variances expressions [SQL Server], statistical variance VAR function [Transact-SQL]
ms.assetid	71dfc339-16c8-42f9-8555-ad45400f7f9b
caps.latest.revision	37
author	BYHAM
ms.author	rickbyh
manager	jhubbard

VAR (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause.

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
VAR ( [ ALL | DISTINCT ] expression )   
   OVER ( [ partition_by_clause ] order_by_clause )    

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
-- Aggregate Function Syntax   
VAR ( [ ALL | DISTINCT ] expression )  
  
-- Analytic Function Syntax  
VAR (expression) OVER ( [ partition_by_clause ] order_by_clause)  

Arguments

ALL
Applies the function to all values. ALL is the default.

DISTINCT
Specifies that each unique value is considered.

expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause**)**
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

Return Types

float

Remarks

If VAR is used on all items in a SELECT statement, each value in the result set is included in the calculation. VAR can be used with numeric columns only. Null values are ignored.

VAR is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

Examples

A: Using VAR

The following example returns the variance for all bonus values in the SalesPerson table in the [!INCLUDEssSampleDBnormal] database.

SELECT VAR(Bonus)  
FROM Sales.SalesPerson;  
GO  

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

B: Using VAR

The following example returns the statistical variance of the sales quota values in the table dbo.FactSalesQuota. The first column contains the variance of all distinct values and the second column contains the variance of all values including any duplicates values.

-- Uses AdventureWorks  
  
SELECT VAR(DISTINCT SalesAmountQuota)AS Distinct_Values, VAR(SalesAmountQuota) AS All_Values  
FROM dbo.FactSalesQuota;  

[!INCLUDEssResult]

Distinct_Values All_Values

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

159180469909.18 158762853821.10

C. Using VAR with OVER

The following example returns the statistical variance of the sales quota values for each quarter in a calendar year. Notice that the ORDER BY in the OVER clause orders the statistical variance and the ORDER BY of the SELECT statement orders the result set.

-- Uses AdventureWorks  
  
SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,  
       VAR(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Variance  
FROM dbo.FactSalesQuota  
WHERE EmployeeKey = 272 AND CalendarYear = 2002  
ORDER BY CalendarQuarter;  

[!INCLUDEssResult]

Year Quarter SalesQuota Variance

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

2002 1 91000.0000 null

2002 2 140000.0000 1200500000.00

2002 3 70000.0000 1290333333.33

2002 4 154000.0000 1580250000.00

See Also

Aggregate Functions (Transact-SQL)
OVER Clause (Transact-SQL)