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 @FirstName AS VARCHAR(50) = ‘BASAVARAJ’ SELECT @FirstName	Non-Unicode large Variable Length character data type.Example: DECLARE @FirstName AS VARCHAR(Max)= ‘BASAVARAJ’ SELECT @FirstName
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, FirstName VARCHAR(50)) GO CREATE INDEX IX_EmployeeFirstName ON dbo.Employee(FirstName) 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, FirstName VARCHAR(Max)) GO CREATE INDEX IX_EmployeeFirstName ON dbo.Employee(FirstName) GO  Error Message: Msg 1919, Level 16, State 1, Line 1 Column ‘FirstName’ 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 @FirstName AS VARCHAR(50) = ‘BASAVARAJ’ SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length BASAVARAJ 9	It takes 1 byte per characterExample: DECLARE @FirstName AS VARCHAR(MAX)= ‘BASAVARAJ’ SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName 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.