Prime Study Hub

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.