?
Solved

Get File Size of "Image" data type in SQL Server

Posted on 2006-11-20
4
Medium Priority
?
398 Views
Last Modified: 2008-02-01
I have a column that is of "Image" data type. I store my pdf documents in this column. I would like to run a query to determine the total fize size in (mb) of my documents. How do I go about doing this?

Thanks,
0
Comment
Question by:tinman1412
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17986236
I think that should be done at the time of inserting the data.
Now you can do it use textcopy command to extract the file from image column into physical file and check the size..

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
  [/D [database]] [/T table] [/C column] [/W"where clause"]
  [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

  /S sqlserver       The SQL Server to connect to. If 'sqlserver' is not
                     specified, the local SQL Server is used.
  /U login           The login to connect with. If 'login' is not specified,
                     a trusted connection will be used.
  /P password        The password for 'login'. If 'password' is not
                     specified, a NULL password will be used.
  /D database        The database that contains the table with the text or
                     image data. If 'database' is not specified, the default
                     database of 'login' is used.
  /T table           The table that contains the text or image value.
  /C column          The text or image column of 'table'.
  /W "where clause"  A complete where clause (including the WHERE keyword)
                     that specifies a single row of 'table'.
  /F file            The file name.
  /I                 Copy text or image value into SQL Server from 'file'.
  /O                 Copy text or image value out of SQL Server into 'file'.
  /K chunksize       Size of the data transfer buffer in bytes. Minimum
                     value is 1024 bytes, default value is 4096 bytes.
  /Z                 Display debug information while running.
  /?                 Display this usage information and exit.

You will be prompted for any required options you did not specify.
0
 
LVL 8

Accepted Solution

by:
doobdave earned 200 total points
ID: 17986312
Hi,
You can use the DATALENGTH() function to get the number of bytes used by the contents of a text/binary field (or any field for that matter).

Eg:
SELECT DATALENGTH(imagefield) FROM tablename WHERE pkcol = somevalue

That will give you the number of BYTES used.

If you want to get a total for all rows in the table then:
SELECT SUM(DATALENGTH(imagefield)) FROM tablename

Best Regards,

David
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17986392
Hi David,

I check it and i extract one of the image from database the size of the physical file was
121498 = 118 kb  and when i check with datalength i get 233249      = 227 kb i don't know why this difference perhaps you have an idea

Regards

Imran

0
 
LVL 8

Expert Comment

by:doobdave
ID: 17986524
I'm not too sure, but here's my theory:

The DATALENGTH returns the number of BYTES used to store the data within the database... this is therefore how much space it uses to store the data.
Perhaps when you extract the contents oof the column and make it into an image again (or a pdf, or whatever), the actual binary data is converted in some way (which must indeed happen) and depending on the type of object it can take up more or less physical disk space.

Therefore, tinman1412, it all depends on whether you need to know the size of the object in physical form, as it were, or the amount of space required to store it in SQL server.
If it is the latter, then the method I suggested will work;

However, imran, I've just done some tests on the Northwind database, and the sizes seem to match so I'm not sure what's going on there.

Best Regards,

David
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question