SSMS select count 3billion errors out -

Question

Hi there,

First time one of my tables is 3 Billion records.

When i queried table count in SSMS

select count(*) from table i got the below error.

Does it mean SSMS cant provide row count for tables greater than 2.5 Billion (max int range) rows?

How to go around it? i understand if its year 1999 for SSMS.. but in year 2024?

or is it my setting?

Thanks

Answer 1

Hi RJ,

Thanks for posting your question in the Microsoft Q&A forum.

The issue you're encountering with SSMS when trying to count 3 billion records is related to the limitations of the INT data type, which has a maximum value of approximately 2.14 billion.

The simplest solution is to cast the result to BIGINT

SELECT CAST(COUNT(*) AS BIGINT) FROM YourTable;

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Answer 2

Does it mean SSMS cant provide row count for tables greater than 2.5 Billion (max int range) rows?

Rigth, therefore you have to use th COUNT_BIG function, it returns the data type bigint, see

https://learn.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql?view=sql-server-ver16