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:

  1. CREATE DATABASE DEMOS  
  2. USE DEMOS  
  3.   
  4. CREATE TABLE #TEMP_COUNT_COUNT_BIG  
  5. (  
  6. ID INT IDENTITY (1, 1),  
  7. NAME AS (CASE WHEN ID%2=0 THEN ‘RAKI’+CAST (ID AS VARCHAR (100)) ELSE 'RAKESH'+CAST (ID AS VARCHAR (100)) END),  
  8. SALARY INT DEFAULT 1000  
  9. )  
  10.   
  11. --INSERT DATA USING GO STATEMENT 50 RECORDS  
  12. INSERT INTO #TEMP_COUNT_COUNT_BIG DEFAULT VALUES   
  13. GO 50  
  14.   
  15. --CHECK DATA  
  16. SELECT * FROM #TEMP_COUNT_COUNT_BIG   
result
  1. SELECT COUNT(NAMEAS [COUNT_NAME],  
  2. COUNT_BIG(NAMEAS [COUNT_BIG_NAME]   
  3. INTO COUNT_BIG_COUNT FROM #TEMP_COUNT_COUNT_BIG   
  4.   
  5. EXEC SP_HELP COUNT_BIG_COUNT  
query
Check the Output:


Output