Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

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

Posted on 2006-11-20
4
Medium Priority
?
401 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

647 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