from 'image' to 'varbinary(max)'

[Gilad]

Hi

I moved to SQL Server 2005 (from 2000) and noticed there is a better
variable to deal with binary arrays.
I hava a table that hold 9 columns of images (BLOB). each array has a
different size and can be larger than 4k.
I've changed the column data type from image to varbinary(max).
After the chnage, the size of the table grew from 22MB to 26MB.
Any idea why? I though the new variable should be better.

I have another table where there are 3 columns with binaries, the size
of the arrays there is much smaller and vary from 32bytes to 150bytes
long. when I changed the data type to varbinary the size of the table
shrunk by half!!

now I'm completely confused...

Gilad.

[Erland Sommarskog]

Gilad (gi*********@gmail.com) writes:

I moved to SQL Server 2005 (from 2000) and noticed there is a better
variable to deal with binary arrays.
I hava a table that hold 9 columns of images (BLOB). each array has a
different size and can be larger than 4k.
I've changed the column data type from image to varbinary(max).
After the chnage, the size of the table grew from 22MB to 26MB.
Any idea why? I though the new variable should be better.

varbinary(MAX) is better than image, because you can work with just like
you work with regular varbinary, while image has a lot of restrictions
and drives you to complex operations like READTEXT and WRITETEXT.

I can't tell why your table increased in size, but since 26 MB is still
a small table, I would not bother.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx