Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type

Ideally, it is better to compare Text and Varchar(MAX) data types, as in Sql Server 2005 Varchar(MAX) data type was introduced as an alternate for Text data type. Varchar(Max) data type provides multiple advantages over Text data type.

Like many initially when Varchar(MAX) datatype was introduced in Sql Server 2005, I too was not clear about the difference between Varchar and Varchar(Max) and which one to use when. Hope the differences listed in the below table clarifies these queries.

Varchar[(n)] Varchar(Max)
Basic Definition Non-Unicode Variable Length character data type.
Example:

DECLARE @Name VARCHAR(50)
         = 'BASAVARAJ'
SELECT @Name
Non-Unicode large Variable Length character data type.
Example:

DECLARE @Name VARCHAR(Max)
         = 'BASAVARAJ'
SELECT @Name
 Storage Capacity It can store maximum 8000 Non-Unicode characters (i.e. maximum storage capacity is 8000 bytes of storage). Optional Parameter n value can be from 1 to 8000. It can store maximum of 2 147 483 647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).
Index? You can create index on Varchar column data type.
Example:

CREATE TABLE dbo.Employee
(id INT identity(1,1)
   PRIMARY KEY,
 Name VARCHAR(50))
GO
CREATE INDEX IX_EmployeeName 
 ON dbo.Employee(Name)
GO
Index can’t be created on a Varchar(Max) data type columns.
Example:

CREATE TABLE dbo.Employee
(id INT identity(1,1)
   PRIMARY KEY,
 Name VARCHAR(Max))
GO
CREATE INDEX IX_EmployeeName
 ON dbo.Employee(Name)
GO 

Error Message:
Msg 1919, Level 16, State 1, Line 1 Column ‘Name’ in table ‘dbo.Employee’ is of a type that is invalid for use as a key column in an index.

How data is stored Physically? It uses the normal data pages to store the data i.e. it stores the value ‘in a row’. Sql server will try to store the value ‘in a row’ but if it could not then it will store the value ‘out of row’. i.e. It uses the normal data pages until the content actually fills 8k of data.When overflow happens, data is stored as old TEXT Data Type and a pointer is replacing the old content.
No. of Bytes required for each character It takes 1 byte per character
Example:

DECLARE @Name VARCHAR(50)
         ='BASAVARAJ'
SELECT @Name Name,
 DATALENGTH(@Name) Length

Result:
Name Length
BASAVARAJ 9

It takes 1 byte per character
Example:

DECLARE @Name VARCHAR(MAX)
         ='BASAVARAJ'
SELECT @Name Name, 
 DATALENGTH(@Name) Length

Result:
Name Length
BASAVARAJ 9

Which one to use? If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then we can use this data type.For example First Name, Last Name etc, columns value can’t cross the max 8000 characters limit, in such scenario’s it is better to use this data type. If we know that the data to be stored in the column or variable can cross a 8KB Data page, then we can use this data type.
Performance There is not much performance difference between Varchar[(n)] and Varchar(Max). Varchar[(n)] provides better performance results compared to Varchar(Max). If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then using this Varchar[(n)]  data type provides better performance compared to Varchar(Max).Example: When I ran the below script by changing the variable @FirstName type to Varchar(Max) then for 1 million assignments it is consistently taking double time than when we used data type as Varchar(50) for variable @ FirstName.

DECLARE @FirstName VARCHAR(50), @COUNT INT=0, 
        @StartTime DATETIME = GETDATE()
WHILE(@COUNT < 1000000)
BEGIN
   SELECT @FirstName = 'BASAVARAJ', @COUNT = @COUNT +1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO 6

Note: Here GO 6 statement executes the statements above it 6 times.

[ALSO READ] You may like to read below other popular articles on differences

1. Varchar vs NVarchar
2. Varchar vs Varchar(MAX)
3. Char vs Varchar
4. Text vs Varchar(Max)
5. Union vs Union All
6. DateTime vs DateTime2
7. SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
8. Stored Procedure vs User Defined Function
9. Primary Key vs Unique Key
10. RAISERROR vs THROW
11. Temporary Table vs Table Variable
12. Len() vs Datalength()
13. Sequence vs Identity
14. DATEDIFF vs DATEDIFF_BIG

24 thoughts on “Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type

Leave a Reply

Your email address will not be published. Required fields are marked *