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: |
| 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: |
It takes 1 byte per character Example:
DECLARE @Name VARCHAR(MAX)
='BASAVARAJ'
SELECT @Name Name,
DATALENGTH(@Name) Length
Result: |
| 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