What Data Can Be Stored in Varbinary Data Type of SQL Server

What data can be stored in varbinary data type of SQL Server?

A varbinary column can store anything. To store a string in it, you'd have to cast it to varbinary:

declare @t table (id int identity, pwd varbinary(50))
insert into @t (pwd) values (cast('secret' as varbinary(50)))

But for a password, a varbinary column usually stores a hash of some kind. For example, a SHA1 hash using the HashBytes function:

insert into @t (pwd) values (HashBytes('sha1', 'secret'));

Storing a one-way hash instead of the real password is more secure. You can check if the password matches:

select * from @t where pwd = HashBytes('sha1', 'secret')

But there is no way you can retrieve the password by looking at the table. So only the end user knows his password, and not even the DBA can retrieve it.

How does varbinary datatype work to store image for a user to view in Access front end?

A VARBINARY field in SQL server is a field that stores binary data, and is often used for storing files. You can fit entire files in this field.

If you have a VARBINARY field in SQL server, and create a linked table in Access to the table with that field, it gets interpreted as an OLE object, since OLE objects are binary data too.

You can use the Bound object frame control in Access to save images into OLE objects, both as an image (which is displayable), or as a package. This does not require any code. You can right click the field -> Insert Object -> Bitmap Image to insert bitmap images into an OLE field. However, this stores OLE object data along with the image data, which makes it very hard to work with using code, and nearly impossible in non-vba applications. Using this approach is only justifiable if you're sure you're going to stick with Access for the lifetime of the database, in my opinion.

I've shared an example on working with VBA code and the OLE object here, but that uses hacky code that executes GUI operations. It's nearly impossible to port to other applications. I recommend you use the next way instead.

Alternatively, you can directly store binary data in the OLE object/Varbinary field. This makes it a lot easier to deal with using VBA code or any future application, since it's just the file data stored in the field, there's no OLE object data stored with it. I've shared code to load binary data in a field and save it back to disk here.

The hard part of this puzzle, if you're just working with binary image data, is displaying the image to the user. I've shared 3 approaches here, with code for one one of them. However, that's quite complex VBA code.

What SQL Server Datatype Should I Use To Store A Byte[]

varbinary(1024) is what you're looking for.

There are three types in SQL Server for binary value storage:

binary(n) for fixed-length binary data of length n. Length can be 1 to 8000.

varbinary(n) for variable-length binary data maximum length n. Maximum length can be 1 to 8000.

Above types will be stored in the row data itself.
varbinary(max) which is used to store large binary values (BLOBs) up to 2GB. The actual value is stored in a separate location if it's larger than 8000 bytes and just a pointer is stored in the row itself. This type is available since SQL Server 2005.

image data type was used to store BLOBs prior to SQL Server 2005. It's deprecated in favor of varbinary(max). The storage location for image is always outside data row.

VarBinary vs Image SQL Server Data Type to Store Binary Data?

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.

Filtering any data type stored in varbinary in sql server

After working around the problem I have understood that there is no solution. You cannot make SQL Server compare two float values presented as varbinary, for example, without casting to float.

As it is impossible to filter data stored in one varbinary column I have created four more columns that contain SQL Server base data types (int, float, datetime, nvarchar). Each user data type is now mapped to SQL Server base type.

Now I can filter any user type (except varbinary, but I do not need to filter images or files) and each row uses only one column related to that type (others have NULL values).

Is there a big technical difference between VARBINARY(MAX) and IMAGE data types?

They store the same data: this is as far as it goes.

"image" is deprecated and has a limited set of features and operations that work with it. varbinary(max) can be operated on like shorter varbinary (ditto for text and varchar(max)).

Do not use image for any new project: just search here for the issues folk have with image and text datatypes because of the limited functionality.

Examples from SO: One, Two

---

Related Topics