Difference between COUNT and COUNT_BIG in SQL

Count

Count Function returns value that data type is INT.

Count_ BIG

Count_Big Function returns value that data type is BIG_INT.

Demonstration:

CREATE DATABASE DEMOS
USE DEMOS

CREATE TABLE #TEMP_COUNT_COUNT_BIG
(
ID INT IDENTITY (1, 1),
NAME AS (CASE WHEN ID%2=0 THEN ‘RAKI’+CAST (ID AS VARCHAR (100)) ELSE 'RAKESH'+CAST (ID AS VARCHAR (100)) END),
SALARY INT DEFAULT 1000
)

--INSERT DATA USING GO STATEMENT 50 RECORDS
INSERT INTO #TEMP_COUNT_COUNT_BIG DEFAULT VALUES
GO 50

--CHECK DATA
SELECT * FROM #TEMP_COUNT_COUNT_BIG

SELECT COUNT(NAME) AS [COUNT_NAME],
COUNT_BIG(NAME) AS [COUNT_BIG_NAME]
INTO COUNT_BIG_COUNT FROM #TEMP_COUNT_COUNT_BIG

EXEC SP_HELP COUNT_BIG_COUNT

Check the Output: