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.
發佈於:施耐德電機Taiwan
