Difference between varchar and varchar(max) in SQL Server
The purpose of this post is to make it clear what to use and when with respect to varchar data type in SQL Server.
Varchar(max) was introduced in SQL Server 2005 version. A few years back, I wasn’t clear about the Difference between varchar and varchar max. It was always confusing for me which one to use.Difference between varchar and varchar(max) in SQL Server Click To Tweet
- 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) = ‘DIPENDRA SHEKHAWAT‘ SELECT @Name
- The maximum storage capacity for varchar is 8000 bytes.
- You can create index on 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.
- Non-Unicode large variable length character data.
- varchar(max) is a constant, it has a value of max.
- Example – DECLARE @Name AS VARCHAR(MAX) = ‘DIPENDRA SHEKHAWAT‘ SELECT @Name
- The maximum storage capacity for varchar(max) is 2147483647 characters (2 GB).
- You cannot create index on 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.
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!
This is a guest post by Dipendra. If you would like to write for DotnetCrunch, check our guest posting guidelines.
What do you think?
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.