APPROX_COUNT_DISTINCT Function in SQL Server(TSQL)

dbtut

5 years ago

This function returns an estimated number of non-null unique values in a data set. It came with SQL Server 2019. Let’s create a table with the Identitiy column and add a few records to this table.

CREATE TABLE [dbo].[MyTable1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[MyTable1] VALUES ('Nurullah CAKIR'),
('Faruk ERDEM'),
('Ogun OZALP'),
('Hakan GURBASLAR'),
('Dilara OZDEMIR')
Select * FROM [dbo].[MyTable1]

CREATE TABLE [dbo].[MyTable1](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL

) ON [PRIMARY]

INSERT INTO [dbo].[MyTable1] VALUES ('Nurullah CAKIR'),

('Faruk ERDEM'),

('Ogun OZALP'),

('Hakan GURBASLAR'),

('Dilara OZDEMIR')

Select * FROM [dbo].[MyTable1]

You can use the APPROX_COUNT_DISTINCT Function as follows.

SELECT APPROX_COUNT_DISTINCT(ID) FROM [dbo].[MyTable1];

1	SELECT APPROX_COUNT_DISTINCT(ID) FROM [dbo].[MyTable1];

Since this function comes with SQL Server 2019, you will get the error as follows when you want to run this function in a previous version.

Msg 195, Level 15, State 10, Line 14

‘APPROX_COUNT_DISTINCT’ is not a recognized built-in function name.

You may also receive a “is not a recognized built-in function name” error if you did not specify the path of the function correctly.