Solved

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

Posted on 2006-11-20
4
390 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

20 Experts available now in Live!

Get 1:1 Help Now