Character & String Data Types
SQL Server provides character and string data types to store text, names, addresses, descriptions, codes and other character-based information.
What are Character & String Data Types?
Character and string data types are used to store text values in SQL Server. These values can contain letters, numbers, spaces and special characters.
SQL Server provides different string data types depending on the maximum length of the text and whether Unicode characters are required.
Common Character & String Data Types
| Data Type | Description | Example |
|---|---|---|
| CHAR(n) | Stores fixed-length non-Unicode character data. | CHAR(10) |
| VARCHAR(n) | Stores variable-length non-Unicode character data. | VARCHAR(100) |
| VARCHAR(MAX) | Stores variable-length character data with very large size. | VARCHAR(MAX) |
| NCHAR(n) | Stores fixed-length Unicode character data. | NCHAR(10) |
| NVARCHAR(n) | Stores variable-length Unicode character data. | NVARCHAR(100) |
| NVARCHAR(MAX) | Stores large variable-length Unicode text. | NVARCHAR(MAX) |
CHAR
The CHAR data type is used to store fixed-length character data.
If the defined length is 10 characters, SQL Server allocates space for 10 characters even when the stored value is shorter.
CREATE TABLE Employees
(
EmployeeCode CHAR(10),
EmployeeName VARCHAR(100)
);
VARCHAR
The VARCHAR data type stores variable-length non-Unicode character data.
It is commonly used for names, email addresses, cities, addresses and other normal text values.
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
City VARCHAR(50),
Email VARCHAR(150)
);
NCHAR and NVARCHAR
NCHAR and NVARCHAR are used to store Unicode character data.
They are useful when applications need to store text from multiple languages and character sets.
CREATE TABLE Customers
(
CustomerId INT,
CustomerName NVARCHAR(100),
Address NVARCHAR(250)
);
CHAR vs VARCHAR
| CHAR | VARCHAR |
|---|---|
| Fixed-length data | Variable-length data |
| Suitable when values have a fixed size | Suitable when values have different lengths |
| Example: Country Code | Example: Student Name |
Example: Using String Data Types
The following example creates a Students table using different character and string data types.
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Gender CHAR(1),
City VARCHAR(50),
Address NVARCHAR(250)
);
Here, different string data types are selected according to the type of text and storage requirement.
Insert String Values
INSERT INTO Students
(
StudentId,
StudentName,
Gender,
City,
Address
)
VALUES
(
1,
'Rahul',
'M',
'Pune',
N'Pune, Maharashtra'
);
Output
StudentId StudentName Gender City Address
-------------------------------------------------------
1 Rahul M Pune Pune, Maharashtra
The output shows how SQL Server stores character and string values in the respective columns.
Choosing the Correct String Data Type
Select the string data type according to the length, structure and character requirements of the data.
- Use CHAR for fixed-length values.
- Use VARCHAR for variable-length non-Unicode text.
- Use NCHAR for fixed-length Unicode text.
- Use NVARCHAR for variable-length Unicode text.
- Use VARCHAR(MAX) or NVARCHAR(MAX) when very large text needs to be stored.
Summary
In this section, we learned about character and string data types in SQL Server, including CHAR, VARCHAR, VARCHAR(MAX), NCHAR, NVARCHAR and NVARCHAR(MAX). We also learned the difference between fixed-length, variable-length and Unicode string data types.