How is an Image Data Type Stored

[amanx]

Hello,

Does anyone know how SQL Server stores an Image data type.
Meaning ->

If my images are around 2k and the image field max is 500k, will this small image take up only 2k of space or would it use the entire 500k?

thx for you help.

 

[JamesLean]

The image data type holds variable-length binary data up to approx 2GB in size. The actual binary data is stored in a separate group of data pages to that which the table rows are stored in. A 16 byte pointer is then stored in the actual row that tells SQL Server where to find the data.

As the data is variable-length, it will only use as much space as it needs, ie a 2k image will only take 2k of storage space.

--James

 

[amanx]

Thx for the input. The reason I asked was we have a db (which we use to store small images) that has grown to 1.5 gig. But there really is not enough data to account for this size.

I copied this db to my test env and ->
Deleted the data out of almost every table (except for seed data) and the db still remains .5 gig. I used delete from statements to remove the data from the tables

We have a copy of this same db with the same structure and seed data and its only 1000k.

I've run DBCC shrinkdatabase - but no luck.

Any one have any thoughts on why its so large - even after the removal of data?

thx

 

[SQLBill]

Run this command in the database....

EXEC SP_SPACEUSED

This should show you where the size is coming from. It might be your indexes.

-SQLBill

 

[Corran007]

is this sql 7 or sql 2000?

If its 7, there is a bug where image space is not able to be restored for use to the database. it does not exist in 2k