Unicode Character Data Types in SQL Server Transact-SQL

Q

What are Unicode character string data types supported in SQL Server Transact-SQL?

✍: FYIcenter.com

A

Unicode character string data types are used to hold Unicode character strings.

There are 3 Unicode character string data types supported in SQL Server Transact-SQL:

1. NCHAR (or NATIONAL CHAR, or NATIONAL CHARACTER) - Used to hold Unicode character strings of a fixed length specified in the format of NCHAR(n), where n defines the string length as number of characters. The maximum length of VARCHAR is 4,000 Unicode characters.

The storage size of VARCHAR(n) is fixed to 2*n bytes, regardless of the length of the string value. Each Unicode character is stored in UTF-16 2-byte encoding.

2. NVARCHAR (or NATIONAL CHAR VARYING or NATIONAL CHARACTER VARYING) - Use to hold Unicode character strings of variable lengths, specified in the format of NVARCHAR(n), where n defines the string length as number of characters. The maximum length of NVARCHAR is 4,000 Unicode characters.

The storage size of NVARCHAR(n) is flexible up to 2*n+2 bytes, depending on the length of the string value. The extra 2 bytes are used to store the length of the string value.

3. NTEXT (or NVARCHAR(MAX), or NATIONAL TEXT) - Use to hold Unicode character strings of very large lengths. NTEXT uses the CLOB (Character Large OBject) technology to store string values. NTEXT can store string values of variable lengths up to 2^30-1 (1,073,741,823) characters.

Here are some good examples of Unicode character string values:

PRINT N'Hello!    '; -- NCHAR(10)
PRINT N'Hello!';  -- NVARCHAR(10)
PRINT CAST(N'Hello!' AS BINARY(10));
----------------------
Hello!    
Hello!
0x480065006C006C006F00

 

Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-04-08, 294👍, 0💬