SQL String Data Types

This section details the string data types available in Microsoft SQL Server. These data types are used to store character data, such as text, names, and addresses.

Character String Data Types

SQL Server offers several data types for storing character strings, each with different characteristics regarding storage size, character set support, and length limitations.

1. `VARCHAR`

Stores non-Unicode character data. The storage size is the actual length of the data entered plus two bytes for overhead. You can specify a maximum length from 1 to 8000 bytes. If a length is not specified, it defaults to 1.

DECLARE @myString VARCHAR(50);
SET @myString = 'Hello, SQL!';
SELECT @myString;

2. `NVARCHAR`

Stores Unicode character data. Each character uses two bytes of storage. The storage size is twice the actual length of the data entered plus two bytes for overhead. You can specify a maximum length from 1 to 4000 characters. If a length is not specified, it defaults to 1.

DECLARE @myUnicodeString NVARCHAR(100);
SET @myUnicodeString = N'你好，世界！';
SELECT @myUnicodeString;

3. `CHAR`

Stores fixed-length non-Unicode character data. If the data entered is shorter than the specified length, it is padded with spaces on the right. The storage size is the specified length in bytes plus two bytes for overhead. You can specify a length from 1 to 8000 bytes. If a length is not specified, it defaults to 1.

DECLARE @fixedString CHAR(10);
SET @fixedString = 'SQL';
SELECT @fixedString; -- Output will be 'SQL       '

4. `NCHAR`

Stores fixed-length Unicode character data. If the data entered is shorter than the specified length, it is padded with spaces on the right. The storage size is twice the specified length in bytes plus two bytes for overhead. You can specify a length from 1 to 4000 characters. If a length is not specified, it defaults to 1.

DECLARE @fixedUnicode NCHAR(5);
SET @fixedUnicode = N'DB';
SELECT @fixedUnicode; -- Output will be N'DB   '

5. `TEXT` (Deprecated)

Stores large character data. Similar to `VARCHAR` but with a larger capacity (up to 2 GB). It is recommended to use `VARCHAR(MAX)` instead of `TEXT` for new development.

6. `NTEXT` (Deprecated)

Stores large Unicode character data. Similar to `NVARCHAR` but with a larger capacity (up to 2 GB). It is recommended to use `NVARCHAR(MAX)` instead of `NTEXT` for new development.

7. `VARCHAR(MAX)`

Stores variable-length non-Unicode character data with a maximum capacity of 2^31-1 bytes (2 GB).

8. `NVARCHAR(MAX)`

Stores variable-length Unicode character data with a maximum capacity of 2^31-1 characters (2 GB).

Choosing the Right String Data Type

• Use VARCHAR or CHAR for data that does not require Unicode support.
• Use NVARCHAR or NCHAR for data that may contain characters from various languages and symbols (Unicode support).
• VARCHAR(MAX) and NVARCHAR(MAX) are suitable for very large text fields.
• CHAR and NCHAR are useful when you need fixed-length strings, but they can be less space-efficient if data lengths vary significantly.

Collation

String data types are affected by the database's or column's collation settings, which determine rules for sorting and character comparison (case sensitivity, accent sensitivity, etc.).

Data Type	Description	Max Length	Storage
VARCHAR	Variable-length, non-Unicode	8000 bytes	Actual length + 2 bytes
NVARCHAR	Variable-length, Unicode	4000 characters	2 * Actual length + 2 bytes
CHAR	Fixed-length, non-Unicode	8000 bytes	Specified length + 2 bytes
NCHAR	Fixed-length, Unicode	4000 characters	2 * Specified length + 2 bytes
VARCHAR(MAX)	Variable-length, non-Unicode	2 GB	Actual length + 2 bytes
NVARCHAR(MAX)	Variable-length, Unicode	2 GB	2 * Actual length + 2 bytes

For more detailed information, refer to the official SQL Server documentation on character string data types.