dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted February 4, 2019

Getting Approximate Count in Microsoft SQL Server

By Greg Larsen

With the introduction of SQL Server 2019, there is now a new, faster way to get a list of distinct values in a column. This new way is using the APPROX_COUNT_DISTINCT function. This new function does not return the exact number of distinct values, but instead, as the function name suggests, it only returns an approximate count. It does this by using the HyperLogLog algorithm.

This new function is faster than the COUNT(*) distinct function because it uses less memory. This new function can estimate the number of distinct values greater than 1,000,000,000, while using less than 1.5 KB or memory. Because it uses less memory, less data is spilled to tempdb than the COUNT(*) function. But there is a cost associated using this new function, and that is accuracy.

The APPROX_COUNT_DISTINCT function does not return the actual number of rows with each distinct value, but instead returns an approximate count. The approximate count might be higher or lower than the actual number. According to Microsoft's documentation, 97% of the time the APPROX_COUNT_DISTINCT function will be within the 2% of the actual value.

If you have a really large table, want a count of distinct value and can live with an approximate count then you might be able to get a performance boost with this new function. Below is an example of how to use this new function:

SELECT APPROX_COUNT_DISTINCT(MyColumn) 
FROM [dbo].[MyTable];


MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM