Varchar and Varchar(max) in SQL Server -DotNetCrunch
VARCHAR and VARCHAR(max): 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.
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 me which one to use.
Let’ 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.
- Non-Unicode large variable-length character data.
VARCHAR(MAX)
- 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.
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!
Happy Programming!
Originally published at https://dotnetcrunch.in on May 2, 2020.
