Varchar and Varchar(max) data types are very important in SQL Server. In this post, we will discuss the differences between varchar and varchar(max) in SQL Server.
The main purpose of this post is to make it clear what to use and when with respect to the varchar and varchar(max) data type in SQL Server.
Topics Covered
Introduction
Varchar(max) was introduced in the SQL Server version 2005. A few years back, I wasn’t clear about the difference between varchar and varchar(max). It was always confusing for most of the developers on which one to use?
Varchar and Varchar(max)
Let us list down their features and find out the differences between the two:
VARCHAR
- Non-Unicode variable-length character data.
- varchar is a variable, you can assign a value to it, it can receive an int from 1 to 8000.
- Example – DECLARE @Name AS VARCHAR(20) = ‘DOTNETCRUNCH’ SELECT @Name
- The maximum storage capacity for varchar is 8000 bytes.
- You can create an index on the varchar column.
- Can be used – If we know that data to be stored in the column or variable is less than or equal to 8000 characters.
VARCHAR(MAX)
- Non-Unicode large variable-length character data.
- varchar(max) is a constant, it has a value of max.
- Example – DECLARE @Name AS VARCHAR(MAX) = ‘DOTNETCRUNCH’ SELECT @Name
- The maximum storage capacity for varchar(max) is 2147483647 characters (2 GB).
- You cannot create an index on the varchar(max) column.
- Can be used – If we know that the data to be stored in the column or variable can cross an 8 Kilo Bytes Data page.
Varchar and Varchar(max) Differences:
Below is the diagrammatic representation of the differences.
NOTE
In terms of performance, there is not much difference between varchar and varchar(max). varchar provides better performance results compared to varchar(max)
That’s all folks. Choose wisely on using varchar in SQL Server. Hope you found this article useful & worth reading.
Thanks for reading and let me know your valuable comments if any.
Like this post? Don’t forget to share it!
[mashshare]
What do you think?
Dear Readers,
If you have any questions or suggestions please feel free to email us or put your thoughts as comments below. We would love to hear from you. If you found this post or article useful then please share along with your friends and help them to learn.
Happy Programming!