{}

Our Brands

Impact-Company-Logo-English Black-01-177x54

Welcome to the Schneider Electric Website

Welcome to our website.

Search FAQs

Approximate Count in Microsoft SQL Server 2019

Issue
The SQL Count(distinct()) function provides the actual row count and may take a very long time to execute on a very large database table.

Product Line
Power Monitoring Expert 9.x

Environment
SQL Server 2019

Cause
The Count(distinct()) function provides the actual row count.  This may take a considerable amount of time with tables containing millions or billions of rows.
This could consume a large amount of time while waiting for the result, if all you require is an approximate row count.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio.
Databases should be backed up prior to performing this procedure.*

SQL Server 2019 introduces the new function Approx_Count_Distinct to provide an approximate count of the rows. 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.

Below is an example of how to use this new function:

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

The above command can be executed by logging into SQL Server Management Studio, entering the command inside a query window while
replacing both 'MyColumn' and 'MyTable' with the values of the column and table you are interested in.

Schneider Electric Malaysia

Explore more
Range:
Users group

Discuss this topic with experts

Visit our Community for first-hand insights from experts and peers on this topic and more.
Explore more
Range: