Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL COUNT overflow

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 expect 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.

like image 546
user593062 Avatar asked Apr 13 '11 02:04

user593062


People also ask

How do you fix an arithmetic overflow error in SQL?

You need to increase the width of the variable to store this number e.g. making @sample NUMERIC (6,2) will solve this error.

How can we avoid arithmetic overflow in SQL?

The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.

What does count (*) do in SQL?

COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.

What is overflowing in SQL?

In general, a data type overflow error is when the data type used to store data was not large enough to hold the data. Furthermore, some data types can only store numbers up to a certain size. An overflow error will be produced, for example, if a data type is a single byte and the data to be stored is greater than 256.


2 Answers

Use COUNT_BIG

SELECT COUNT_BIG(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0 
like image 111
pstrjds Avatar answered Sep 21 '22 23:09

pstrjds


  SELECT COUNT_BIG(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0 
like image 23
Sanjeevakumar Hiremath Avatar answered Sep 24 '22 23:09

Sanjeevakumar Hiremath