VarBinary vs Image SQL Server Data Type to Store Binary Data?
Sql ServerSql Server Problem Overview
I need to store binary files to the SQL Server Database. Which is the better Data Type out of Varbinary and Image?
Sql Server Solutions
Solution 1 - Sql Server
Since image is deprecated, you should use varbinary.
per Microsoft (thanks for the link @Christopher)
> ntext , text, and image data types will be removed in a future > version of Microsoft SQL Server. Avoid using these data types in new > development work, and plan to modify applications that currently use > them. Use nvarchar(max), varchar(max), and varbinary(max) instead. > > Fixed and variable-length data types for storing large non-Unicode and > Unicode character and binary data. Unicode data uses the UNICODE UCS-2 > character set.
Solution 2 - Sql Server
varbinary(max)
is the way to go (introduced in SQL Server 2005)
Solution 3 - Sql Server
There is also the rather spiffy FileStream
, introduced in SQL Server 2008.
Solution 4 - Sql Server
https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql
> image > > Variable-length binary data from 0 through 2^31-1 (2,147,483,647) > bytes. Still it IS supported to use image datatype, but be aware of:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql
> varbinary [ ( n | max) ] > > Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage > size is 2^31-1 bytes. The storage size is the actual length of the > data entered + 2 bytes. The data that is entered can be 0 bytes in > length. The ANSI SQL synonym for varbinary is binary varying.
So both are equally in size (2GB). But be aware of:
Though the end of "image" datatype is still not determined, you should use the "future" proof equivalent.
But you have to ask yourself: why storing BLOBS in a Column?