String Data Types

20 May 2024

|
4 min read
Blog Image

In SQL Server, string data types are used to store textual data. These types can be broadly categorized into character strings (fixed-length and variable-length) and Unicode character strings. Each type is designed for specific use cases, depending on the length and nature of the text to be stored. Here's a detailed explanation of each string data type, with examples and key differences:

Character String Data Types

1. CHAR (Fixed-length)

  • Description: Stores fixed-length non-Unicode character data.
  • Storage Size: The defined length in bytes. For example, CHAR(10) will always use 10 bytes of storage.
  • Example: Suitable for storing fixed-length strings, like country codes.

CREATE TABLE Countries (

CountryCode CHAR(3) PRIMARY KEY,

CountryName VARCHAR(50) NOT NULL

);

2. VARCHAR (Variable-length)

  • Description: Stores variable-length non-Unicode character data.
  • Storage Size: The length of the string plus 2 bytes.
  • Example: Suitable for storing variable-length strings, like user names or descriptions.

CREATE TABLE Users (

UserID INT PRIMARY KEY,

UserName VARCHAR(50) NOT NULL,

Email VARCHAR(100) NOT NULL

);

3. TEXT (Variable-length)

  • Description: Stores large variable-length non-Unicode character data.
  • Storage Size: Up to 2^31-1 (2,147,483,647) characters.
  • Note: TEXT is deprecated and should be avoided in new development. Use VARCHAR(MAX) instead.
  • Example: Used for storing large text fields in older databases.

CREATE TABLE Articles (

ArticleID INT PRIMARY KEY,

Content TEXT NOT NULL

);

From Enthusiast to Expert - Sign Up for Our SQL Server Developer Course at TrainingHub.io!

Unicode Character String Data Types

1. NCHAR (Fixed-length)

  • Description: Stores fixed-length Unicode character data.
  • Storage Size: The defined length in bytes, where each character uses 2 bytes. For example, NCHAR(10) will always use 20 bytes of storage.
  • Example: Suitable for storing fixed-length Unicode strings, like product codes in multiple languages.

CREATE TABLE Products (

ProductCode NCHAR(10) PRIMARY KEY,

ProductName NVARCHAR(100) NOT NULL

);

2. NVARCHAR (Variable-length)

  • Description: Stores variable-length Unicode character data.
  • Storage Size: The length of the string plus 2 bytes, where each character uses 2 bytes.
  • Example: Suitable for storing variable-length Unicode strings, like international user names or descriptions.

CREATE TABLE InternationalUsers (

UserID INT PRIMARY KEY,

UserName NVARCHAR(50) NOT NULL,

Email NVARCHAR(100) NOT NULL

);

3. NTEXT (Variable-length)

  • Description: Stores large variable-length Unicode character data.
  • Storage Size: Up to 2^30-1 (1,073,741,823) characters.
  • Note: NTEXT is deprecated and should be avoided in new development. Use NVARCHAR(MAX) instead.
  • Example: Used for storing large Unicode text fields in older databases.

CREATE TABLE Comments (

CommentID INT PRIMARY KEY,

Comment NTEXT NOT NULL

);

Advance your data analytics career with TrainingHub.io's industry-recognized Data Analyst courses in Toronto.

Differences and Use Cases

Storage Requirements:

  • CHAR and NCHAR use a fixed amount of storage regardless of the actual length of the data.
  • VARCHAR and NVARCHAR use storage based on the actual length of the data plus 2 bytes.
  • Unicode types (NCHAR, NVARCHAR, NTEXT) use more storage because each character takes 2 bytes, compared to non-Unicode types (CHAR, VARCHAR, TEXT).

Performance:

  • Fixed-length types (CHAR, NCHAR) can be faster for performance when the length of the data is known and consistent.
  • Variable-length types (VARCHAR, NVARCHAR) are more space-efficient for data of varying lengths.

Unicode Support:

  • Use Unicode types (NCHAR, NVARCHAR, NTEXT) when storing data that includes characters from multiple languages.
  • Non-Unicode types (CHAR, VARCHAR, TEXT) are suitable for English or other single-byte character sets.

Deprecated Types:

  • Avoid using TEXT and NTEXT in new development work. Use VARCHAR(MAX) and NVARCHAR(MAX) instead for large text storage.

Examples

Using CHAR and VARCHAR

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName CHAR(20) NOT NULL,

LastName VARCHAR(50) NOT NULL

);

  • FirstName uses CHAR(20), meaning it will always store 20 bytes, even if the name is shorter.
  • LastName uses VARCHAR(50), meaning it will store only the number of characters in the name plus 2 bytes.

Using NCHAR and NVARCHAR
CREATE TABLE MultilingualProducts (

ProductID INT PRIMARY KEY,

ProductCode NCHAR(10) NOT NULL,

Description NVARCHAR(255) NOT NULL

);

  • ProductCode uses NCHAR(10), meaning it will always store 20 bytes, supporting Unicode characters.
  • Description uses NVARCHAR(255), meaning it will store only the number of Unicode characters in the description plus 2 bytes.
Master SQL Server with Hands-On Training - Enroll Now at TrainingHub.io! Experience world-class education at TrainingHub.io, recognized as the best IT training institute in Canada.

Recent blogs

Featured Image
GROUP BY Clause

21 June 2024

|
3 min read
Featured Image
CASE Statement

15 June 2024

|
4 min read
Featured Image
ORDER BY Clause

10 June 2024

|
3 min read