sum(DATALENGTH) returning an "Arithmetic overflow" error

Question

I am new to SQL Server so please accept my apologies if my question seems too easy. I tried finding a solution, but so far I couldn't see anything that I could use in my query.

I am trying to find length of the biggest columns in a table and I'm using the following query.

SELECT
    SUM(DATALENGTH([INSDetails])) as [INSDetails]
FROM 
    dbo.Contractors

The table Contractors is slightly over 8GB and has over 30mln rows. INSDetails column is varchar(2048)

The query above works perfectly well for all the other columns of all the other tables in my database, but when I run it on Contractors table it returns an error

Msg 8115, Level 16, State 2, Line 26
Arithmetic overflow error converting expression to data type int.

I understand that this error message appears when you try to convert a value of a certain datatype to another datatype, but that value is too large for the second datatype.

Could you please help me to rewrite the query or suggest alternative approach to get the output?

I read that someone suggested using CAST AS big int to solve this issue, but I'm not sure how it can be incorporated in my query.

Any suggestions will be appreciated. Thank you in advance.

That error is because the value is too large to fit in an int. DATALENGTH will return an INT unless the datatype is a max datatype. Then when you put that inside your SUM it is forcing SUM to return an int. Why do you care how many characters total you have in 30 million rows? — Sean Lange
– Sean Lange, Commented Jun 29, 2015 at 14:49
Thank you @SeanLange I need total number of characters because management decided to compress some columns and they are asking me for the totals. — QWE
– QWE, Commented Jun 29, 2015 at 14:51
See my answer. Not a surprise. You are tying to convert varchar to bigint. — paparazzo
– paparazzo, Commented Jun 29, 2015 at 14:57

Rudey · Accepted Answer · 2021-01-04 10:44:06Z

7

select sum(cast(datalength([INSDetails]) as bigint))

edited Jan 4, 2021 at 10:44

Rudey

5,0255 gold badges47 silver badges89 bronze badges

answered Jun 29, 2015 at 14:53

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sum(DATALENGTH) returning an "Arithmetic overflow" error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related