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.

Text Datatypes

The text has the capacity to store from 1 byte to 4 Gigabytes of data. We need to specify length in char and varchar but in the case of text, we do not need to specify length. Yet text works slower than char and varchar. There are several categories in Text Datatypes:

• TINY TEXT
• TEXT
• MEDIUM TEXT
• LONG TEXT
• NTEXT

TINY TEXT

The non-Unicode is the character string datatype that stores data up to 255 characters. For example, the following statement creates a column called stu_name with a length of 255 characters and we also use TEXT for the large number of characters.

CREATE TABLE GeeksforGeeks (
  id INT NOT NULL AUTO_INCREMENT,
  stu_name TINYTEXT NOT NULL
);

TEXT

It is a non-Unicode, character string datatype storing around 64KB of data. For example, the following statement creates a column called stu_name with a length of 65,535 characters:

CREATE TABLE GeeksforGeeks (
  id INT NOT NULL AUTO_INCREMENT,
  stu_name TEXT NOT NULL
);

MEDIUM TEXT

It stores up to 16MB of data. We can write description-length data with medium text.

LONG TEXT

We can store up to 4GB of data using this non-unicode datatype. Using this data type, we can type data to the length of an article.

NTEXT

A Unicode data type that stores the data without having to specify length. Storage size is double the size that is specified in the column. There are no subcategories for next.

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 GeeksforGeeks (
  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 GeeksforGeeks (
  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.

Conclusion

Finally, we need to talk about binary and textual data types. In the MYSQL server, both datatypes are significant. If we need to store big amounts of data, we use binary files such as LONGTEXT, LONGBLOB, etc.; whereas, for smaller amounts of data, we can use TINYTEXT, TEXT, MEDIUMTEXT, etc.