SQL Server 2000+ which column type to use for storing blobs?

Question

I have to store blob data roughly 10MB per record. Which column type and size should I use for the field?

Answer 1

IMAGE, VARBINARY(MAX)

Answer 2

I would use VARBINARY(MAX) as the IMAGE data type will be dropped in future versions of SQL Server.

Answer 3

And with SQL Server 2008, you could even consider putting your VARBINARY(MAX) columns into their own FILESTREAM filegroup and store them in the file system of your SQL Server machine, instead of inside the database.

This works well for blobs that are routinely over 1 MB in size, or if you ever need to store more than 2 GB (limit for VARBINARY(MAX)) in a single blob.

Marc