SQL COUNT overflow

sql sql-server count overflow
user593062 picture user593062 · Apr 13, 2011 · Viewed 24.9k times · Source

Here is my query:

SELECT COUNT(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0

Here is the result:

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

The table has 4 billion rows. I don't except this query to be fast, but after about 5mins, it fails with an overflow error. Is there a COUNT function for bigger data than int?

Thanks.

Answer

pstrjds picture pstrjds · Apr 13, 2011

Use COUNT_BIG

SELECT COUNT_BIG(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0

Related questions

Fastest way to determine if record exists

As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: SELECT COUNT(*) FROM products WHERE products.id = ?; vs SELECT COUNT(products.…

Read More
Select COUNT(*) of subquery without running it twice

I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page …

Read More
How can you use COUNT() in a comparison in a SELECT CASE clause in Sql Server?

Let's say you want do something along the following lines: SELECT CASE WHEN (SELECT COUNT(id) FROM table WHERE column2 = 4) > 0 THEN 1 ELSE 0 END Basically just return 1 when there's one or more rows in the table, 0 otherwise. There has to …

Read More