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.

Answer 1

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