Aggregate Functions

COUNT vs COUNT_BIG

Both COUNT and COUNT_BIG are SQL Aggregate Functions.

Both COUNT and COUNT_BIG are Deterministic functions when used without the OVER and ORDER BY clauses.
But when used with the OVER and ORDER BY clauses, both COUNT and COUNT_BIG are non-deterministic functions.

Logic

COUNT(), COUNT_BIG()
– Provides the Count of rows that includes Nullable/Duplicate Values (Simply Total rows).

COUNT([Column_name]), COUNT_BIG([Column_name]), COUNT(ALL [Column_name]), COUNT_BIG(ALL [Column_name])
– Provides the Count of rows that includes Non-Nullable/Duplicate Values.

COUNT(DISTINCT [Column_name]), COUNT_BIG(DISTINCT [Column_name])
– Provides the Count of rows that includes Non-Nullable/Unique Values.

Example Code –

IF OBJECT_ID('[Samples]') IS NOT NULL
	DROP TABLE [Samples];

CREATE TABLE [Samples]
(
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] INT NULL
);
GO

INSERT INTO [Samples] ([Value])
VALUES 
(1),(NULL),(1),(2)
;

--OVERVIEW OF COUNT VS COUNT_BIG
SELECT 
	--Includes Nullable/Duplicate Values (Simply Total rows)
	COUNT(*) AS [COUNT],								
	COUNT_BIG(*) AS [COUNT_BIG],						
	--Includes Non-Nullable/Duplicate Values
	COUNT([Value]) AS [COUNT_Column],					
	COUNT_BIG([Value]) AS [COUNT_BIG_Column],			
	COUNT(ALL [Value]) AS [COUNT_ALL],					
	COUNT_BIG(ALL [Value]) AS [COUNT_BIG_ALL],			
	--Includes Non-Nullable/Unique Values
	COUNT(DISTINCT [Value]) AS [COUNT_DISTINCT],		
	COUNT_BIG(DISTINCT [Value]) AS [COUNT_BIG_DISTINCT]	
FROM [Samples];

COUNT vs COUNT_BIG

Both COUNT and COUNT_BIG Function operates in a same way but it differs only in the return type.

  1. The COUNT Function returns INT data type whereas COUNT_BIG Function returns BIGINT data type.
  2. The COUNT Function not supports Indexed View whereas COUNT_BIG Function supports it.

1. Return Datatype

Example Code –

IF OBJECT_ID('[Samples]') IS NOT NULL
	DROP TABLE [Samples];

IF OBJECT_ID('[ReturnDatatype]') IS NOT NULL
	DROP TABLE [ReturnDatatype];

CREATE TABLE [Samples]
(
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] INT NULL
);
GO

INSERT INTO [Samples] ([Value])
VALUES 
(1),(NULL),(1),(2)
;

--RETURN Datatype
SELECT 
	COUNT(*) AS [COUNT],								
	COUNT_BIG(*) AS [COUNT_BIG] INTO ReturnDatatype						
FROM [Samples];
--To check values
SELECT * FROM ReturnDatatype;
-- Checking Schema for Return Datatype Difference
EXEC SP_HELP ReturnDatatype;

2.INDEXED VIEW

Indexed View on COUNT Function

Example Code For Indexed View on COUNT Function

SET NOCOUNT ON;

IF OBJECT_ID('[Samples]') IS NOT NULL
	DROP TABLE [Samples];

CREATE TABLE [Samples]
(
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] INT NULL
);
GO

INSERT INTO [Samples] ([Value])
VALUES 
(1),(NULL),(1),(2)
;
GO

--START OF INDEX VIEW ON COUNT

	--Indexed View Difference
	CREATE VIEW SampleViewname
	WITH SCHEMABINDING
	AS
	SELECT 
		COUNT(*) AS [ColumnName],[Value]											
	FROM dbo.[Samples]
	GROUP BY [Value];
	GO

	-- Create Index on View
	CREATE UNIQUE CLUSTERED INDEX [IX_SampleViewname] ON [dbo].[SampleViewname] ( [Value] ASC );
	GO
	/*
	Msg 10136, Level 16, State 1, Line 32
	Cannot create index on view "TESTDB.dbo.SampleViewname" because it uses the aggregate COUNT. Use COUNT_BIG instead.
	*/
 
--END OF INDEX VIEW ON COUNT

Indexed View on COUNT_BIG Function

Example Code For Indexed View on COUNT_BIG Function

SET NOCOUNT ON;

IF OBJECT_ID('[Samples]') IS NOT NULL
	DROP TABLE [Samples];

CREATE TABLE [Samples]
(
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] INT NULL
);
GO

INSERT INTO [Samples] ([Value])
VALUES 
(1),(NULL),(1),(2)
;
GO

--START OF INDEX VIEW ON COUNT_BIG
	--Indexed View Difference
	CREATE VIEW SampleViewname
	WITH SCHEMABINDING
	AS
	SELECT 
		COUNT_BIG(*) AS [ColumnName],[Value]											
	FROM dbo.[Samples]
	GROUP BY [Value];
	GO
	-- Create Index on View
	CREATE UNIQUE CLUSTERED INDEX [IX_SampleViewname] ON [dbo].[SampleViewname] ( [Value] ASC );
	GO
	EXEC SP_HELP SampleViewname;
--END OF INDEX VIEW ON COUNT_BIG

DROP VIEW SampleViewname;

Thanks for reading!

Advertisement

One thought on “COUNT vs COUNT_BIG

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s