text, ntext, binary, varbinary and varbinary(max) in MS SQL Server
Character (char) and variable character (varchar) are used in storing fixed length of words. These data types are used for creating databases on 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 paragraph of words? NO. For such instances, ‘text’ datatype is used.
Text has capacity to store from 1 byte to 4 Gigabytes of data. We need to specify length in char and varchar but in case of text, we do not have need to specify length. Yet text works slower than char and varchar. There are 4 subcategories :
- tiny text :
It is non-unicode, character string datatype that stores data up to 255 characters.
- text :
It is non-unicode, character string datatype storing around 64KB of data.
- medium text :
It stores up to 16MB of data. We can write description-length data with medium text.
- longtext :
We can store up to 4GB of data using this non-unicode datatype. Using this data type, we can type data to length of an article.
A Unicode data type that stores the data without having to specify length. Storage size is double size that is specified in column. There are no subcategories for ntext.
In a few cases, we might have need to store files, images in database. To store this type of data, there is 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 requirements of users, we can use variable or fixed length.
- 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.
- varbinary :
The name as it suggests, stores variable-length data. Storage depends upon number of bytes specified.
- 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.