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.
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Mean and Mode in SQL Server
- Copy tables between databases in SQL Server using Import-and-Export Wizard
- Difference between MySQL and MS SQL Server
- Difference between Couchbase and MS SQL Server
- Create, Alter and Drop schema in MS SQL Server
- Difference between MS SQL Server and CouchDB
- Difference between MS SQL Server and IBM DB2
- Difference between Derby and MS SQL Server
- Difference between Redis and MS SQL Server
- Full join and Inner join in MS SQL Server
- Left join and Right join in MS SQL Server
- Difference between Neo4j and MS SQL Server
- Self Join and Cross Join in MS SQL Server
- Union and Union All in MS SQL Server
- SIN() and COS() Function in SQL Server
- varchar, varchar(max) and nvarchar in MS SQL Server
- Difference between char and nchar : MS SQL Server Datatypes
- Numeric and Date-time data types in SQL Server
- Deterministic and Nondeterministic Functions in SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.