Solved

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

Posted on 2006-11-20
4
389 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
  • 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 50 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now