What are the string datatypes in SQL?
Datatype in SQL is a property that is used to indicate the type of data a column can contain. Examples of datatypes are:
-
Numeric datatypes
-
Date and time datatypes
-
String datatypes
String datatypes in SQL
String datatypes in SQL allow us to store any kind of data in a table. Users can either store a fixed length of characters or a variable length of characters depending on their preference.
Types of string datatypes in SQL
There are two types of string datatypes in SQL:
-
Character string datatypes
-
Unicode character string datatypes
Character string datatypes in SQL
In SQL, two types of character string datatypes exist. These are:
- Char datatype
- Varchar datatype
Char datatype
The char datatype is used to store a fixed number of characters in SQL. For instance, if a declaration of char(20) is made, then 20 characters are supposed to be held by the memory allocated.
However, if only 10 characters are inserted, then the excess memory allocated gets wasted. The default length is 1, and the maximum length is 65000 octets (bytes).
Varchar datatype
The varchar datatype stores variable characters as per the user’s preference. Thus, it is a variable-length character data type. It is different from the char datatype because it only allocates memory based on the number of characters inserted.
The default length of the varchar datatype is 80, and the maximum length is 65000 octets.
How to set the maximum length of the character data type
The maximum length parameter for VARCHAR and CHAR data types refers to the number of octets that can be stored in that field, not the number of characters. For instance, to store strings of size 24 octets in length, use one of the following definitions:
CHAR(24) /* fixed-length */VARCHAR(24) /* variable-length */
Unicode character string datatypes in SQL
The Unicode character string datatypes in SQL are used in cases where users need to store huge amounts of data. Assuming English to be the default language of a database, the Unicode character string datatypes (nvarchar) will be used to store a foreign language in the SQL server. It is recommended to use nvarchar when the sizes of the data entry columns vary considerably and the string length may be greater than 4,000 byte-pairs. The types of Unicode character string datatypes are:
-
Nchar
-
Nvarchar
String data types
Data type | Definition | Maximum size | Storage limit |
char | fixed width character string | 8,000 characters | defined width |
varchar | variable width character string | 8,000 characters | 2 bytes + number of chars |
nchar | fixed width unicode string | 4,000 characters | defined width x 2 |
nvarchar | variable width unicode string | 4,000 characters | 2 bytes + number of chars x 2 |