Posted by Erland Sommarskog on 04/10/07 21:17

Connie (csawyer@rwbaird.com) writes:
> If I run it like this select sum(datalength (cast(document AS
> varbinary))) from plan_report
> I get 89010 this as a result

Because in this context the default is varbinary(30).

> If I run this: select datalength(document) from plan_report then I
> get a result for each row and when I sum those rows I get 5584452854
> this as a result.

The obvious query would be

SELECT SUM(datalength(document)) FROM plan_report

you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:

SELECT SUM(cast(datalength(document) as bigint)) FROM plan_report


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Retrieve ALL tables from ALL Databases
• Prev in forum: Re: Sum of Column of Blob type
• Thread view: Re: Sum of Column of Blob type

[Reply to this message]