Binary Datatypes
In a few cases, we might need to store files, and images in the database. To store this type of data, there is a datatype named binary that can store this kind of data. It has subtypes that help to store related data according to storage size. Depending on the requirements of users, we can use a variable or fixed length. These are of several types:
- BINARY
- VARBINARY
- VARBINARY(MAX)
BINARY
Binary is fixed-length data type that stores pictures, files, and other media. Storage size depends on the length specified. It can store up to 8000 bytes. For example, the following statement creates a column called image with a length of 2000 bytes so the query is shown given below.
CREATE TABLE w3wiki (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
image1 BINARY(2000)
);
VARBINARY
The name as it suggests, stores variable-length data. Storage depends upon number of bytes specified. For example, the following statement creates a column called files with a variable length so the query is shown below.
CREATE TABLE w3wiki (
id INT NOT NULL,
name VARCHAR(255) NOT NULL,
files VARBINARY(255)
);
VARBINARY(MAX)
It stores the maximum size of up to 2GB. A variable-length datatype, varbinary(max) can be used for media that require large capacity.
text, ntext, binary, varbinary and varbinary(max) in MS SQL Server
Character (char) and variable character (varchar) are used in storing the fixed length of words. These data types are used for creating databases on a small scale. Suppose we have an enterprise that has various products. The database has to store product details including its description. We have char and varchar but will they be able to store paragraphs of words? NO. For such instances, the ‘text’ datatype is used.