VARCHAR, VARCHAR(MAX), and NVARCHAR in MS SQL Server
Last Updated :
12 Aug, 2024
In SQL Server, VARCHAR, VARCHAR(MAX) and NVARCHAR are used to store variable-length text data. VARCHAR is efficient for non-Unicode text up to 8,000 characters, while VARCHAR(MAX) handling larger text data up to 2 GB. NVARCHAR supports Unicode data, making it suitable for multilingual applications.
In this article, We will discuss the VARCHAR, VARCHAR (MAX) and NVARCHAR datatypes with the help of examples in SQL Server.
VARCHAR in MS SQL Server
- Variable Character or VARCHAR for short, is a data type that stores non-Unicode data.
- We should use VARCHAR when the sizes of the column data entries vary considerably.
- It only stores the actual length of the data. If n is not specified, the default length is 1 character.
Syntax
VARCHAR (n)
Here, n is the number of bytes. The maximum storage capacity is up to 8000 bytes. If n is not specified, the default length is 1 character.
VARCHAR(MAX) Datatype in MS SQL Server
- It stores character string data with a maximum storage size of 2³¹-1 bytes.
- It can store up to 2 GB of data, which is significantly larger than the VARCHAR(n) data type.
Syntax
VARCHAR(max)
The NVARCHAR datatype
This stores variable length unicode data. NVARCHAR is used when there is variability in the length of the data, such as storing names in different languages.
Syntax
NVARCHAR(n)
Here n is the number of bytes and can store upto 4000 bytes. If the length for the datatype isn't specified, it takes the default value of 1.
Difference Between VARCHAR, VARCHAR(MAX) AND NVARCHAR
Let’s learn the differences between VARCHAR, VARCHAR(MAX) and NVARCHAR. We’ll see what makes each one unique, how they’re different from each other, and why we might use one over the other.
It Does not support Unicode
| Parameter | VARCHAR | VARCHAR(MAX) | NVARCHAR |
|---|
| Definition | Variable-length character data type | Variable-length character data type with a maximum size of 2 GB | Variable-length Unicode character data type |
| Storage | Stores up to 8,000 characters | Stores up to 2 GB of characters | Stores up to 4,000 characters (or more with NVARCHAR(MAX)) |
| Length Specification | Length specified is the maximum possible size | Length can be up to 2 GB | Length specified is the maximum possible size (or up to 2 GB with NVARCHAR(MAX)) |
| Storage Efficiency | Efficient for varying-length data up to 8,000 characters | Efficient for very large text data | Efficient for varying-length Unicode data |
| Performance | Generally efficient for text up to 8,000 characters | Suitable for very large text but may incur overhead for large sizes | Generally efficient for Unicode data up to 4,000 characters (or more) |
| Use Case | Text data where maximum length is known and is less than 8,000 characters | Large text data or very large strings | Unicode text data that requires support for multiple languages |
| Unicode Support | It Does not support Unicode | It Supports Unicode |
| Example | VARCHAR(50) allows storing up to 50 characters | VARCHAR(MAX) allows storing very large text | NVARCHAR(50) allows storing up to 50 Unicode characters |
| SQL Query Example | CREATE TABLE Products (ProductDescription VARCHAR(50)) | CREATE TABLE Documents (Content VARCHAR(MAX)) | CREATE TABLE Users (Name NVARCHAR(100)) |
Conclusion
Overall, Selecting between VARCHAR, VARCHAR(MAX), and NVARCHAR depends on your text data requirements. Use VARCHAR for moderate, non-Unicode text, VARCHAR(MAX) for large text needs, and NVARCHAR for Unicode text to ensure efficient storage and optimal performance.
Similar Reads
text, ntext, binary, varbinary and varbinary(max) in MS SQL Server Character (char) and variable character (varchar) are used in storing the fixed length of words. These data types are used for creating databases on a small scale. Suppose we have an enterprise that has various products. The database has to store product details including its description. We have ch
3 min read
Difference between char, varchar and VARCHAR2 in Oracle 1. CHAR :The char data type is used to store the character values. It is a fixed-length data type i.e. once initialized we cannot change the size at execution time. Hence, it is also called a Static datatype.It is used to store normal characters and alphanumeric characters too. The char data type ca
5 min read
CHAR vs VARCHAR in SQL 1. CHAR Datatype: It is a datatype in SQL which is used to store character string of fixed length specified. If the length of the string is less than set or fixed-length then it is padded with extra blank spaces so that its length became equal to the set length when PAD_CHAR_TO_FULL_LENGTH SQL mode
3 min read
CHARACTER VARYING vs VARCHAR in PostgreSQL In PostgreSQL, the terms CHARACTER VARYING and VARCHAR are often used interchangeably, but are they truly the same? We will understand these data types in this article to clarify their similarities and differences. We'll explore how they work, their syntax, and examples of their usage in PostgreSQL.
6 min read
How to Declare a Variable in SQL Server? In SQL Server, variables play a critical role in the dynamic execution of SQL scripts and procedures. Variables allow you to store and manipulate data temporarily within the scope of a batch or procedure. By using the DECLARE statement, you can create variables with specific data types, which can th
6 min read