Prime Study Hub

The VAR is an aggregate function in SQL Server which returns the variance of all or distinct values of the specified column in SQL Statement.  Let's look into the syntax, Syntax SELECT VAR ( [ ALL | DISTINCT ] expression ) FROM [Table_Name] ALL - It is default. Returns the variance of all values. DISTINCT - Returns the variance of unique values ALL SELECT VAR(ALL ProductID) as ALLProduct FROM [AdventureWorks2019].[Production].[ProductInventory] The above statement returns the variance of ALL product values. DISTINCT SELECT VAR(DISTINCT ProductID) as DistinctProduct FROM [AdventureWorks2019].[Production].[ProductInventory] The above statement returns the variance of DISTINCT  product values. To learn more - Click Here

The COUNT_BIG is an aggregate function in SQL server which returns the number of items within a group. This function works like COUNT function but the only difference is the data type of its return values. The COUNT_BIG function return type is bigint , COUNT function return type is int .  Syntax COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } ) ALL  - It is the default in COUNT_BIG function. This applies the aggregate function to all values. DISTINCT - This returns the number of unique nonnull values. Expression - It is an expression of any type but does not support aggregate functions or subqueries. * - This denotes that all the rows should be counted, including the duplicate rows and null values and returns the total number of rows in a statement. Let's look into an example, SELECT COUNT_BIG(*) AS "Work Order Count" FROM [AdventureWorks2019].[Production].[WorkOrder] Result: ALL SELECT COUNT_BIG(ALL WorkOrderID) AS "Work Order Count" FROM [Adventur...

APPROX_COUNT_DISTINCT is an aggregate function in SQL Server which returns the approximate number of distinct values. This is very efficient with large number of rows.  Syntax APPROX_COUNT_DISTINCT (expression) The return types of this function is bigint . And expression type can be of any type, except image, sql_variant, ntext, or text - as per Microsoft documentation .  Let's look into an example, SELECT APPROX_COUNT_DISTINCT([ProductID]) AS Approx_Distinct_Product FROM [AdventureWorks2019].[Production].[WorkOrder]; The above statement returns the approximate number of different products from WorkOrder table. This is very powerful with billions of rows without any high memory utilization or performance issues.

The ROW_NUMBER() function assigns the integer value to each row of the returned result set which starts at 1 and increments by 1. Let's look into the syntax, ROW_NUMBER() OVER ( [PARTITION BY exp1, exp2 ... expn] ORDER BY col1,col2...coln) The ROW_NUMBER function must have an OVER clause with ORDER BY. ORDER BY clause is mandatory PARTITION BY clause is optional Let's look into an example, SELECT TOP 10 [PersonType] ,[FirstName] ,[LastName] , ROW_NUMBER() OVER(ORDER BY FirstName) as RowNum FROM [AdventureWorks2019].[Person].[Person] For the above example, we are using the [Person] table from [AdventureWorks2019] database. This statement returns the temporary row number for each row based on the order of first name column. Using ROW_NUMBER function with PARTITION BY clause The PARTITION BY clause breaks up the result set into partitions. Once the data is partitioned, row number is reset to 1 when the partition changes. SELECT [ProductID] ,[Name] ...