SQL Server - Varchar Vs Varchar(Max) Data Type
Declaration :
Varchar(n) - varchar declaration of length “n “
Example :
- declare @wikitechy varchar(100) as ‘venkat’
Select @wikitechy
Varchar(max) – declaration of variable with length of max – 2 GB
Example :
- declare @wikitechy varchar(max) as ‘venkat’
Select @wikitechy
Max storage capability :
- Varchar(n) supports only 8000 bytes of data in the field definition.
Example :
- declare @wikitechy varchar (8000) - > max 8000 accepted in this.
- Varchar(max) stores up to 2GB (max data type) characters in the field definition.
Example :
- declare @wikitechy varchar(max) - > max allocates maximum of 2GB of data now
- Store maximum of 2 147 483 647 Non-Unicode characters (maximum storage capacity is: 2GB)
Index creation :
- Varchar(n) supports to create index on the column. I Am trying to create a non-clustered index on the column First Name in the below table.
Example :
CREATE TABLE dbo.wikitechyTable (id INT identity(1,1) PRIMARY KEY, FirstName VARCHAR(50))
GO
CREATE INDEX IX_ wikitechyTableFirstName ON dbo. wikitechyTable (FirstName)
GO
- Varchar(max) won’t permit to create index on the column.
Example :
CREATE TABLE dbo.wikitechyTable (id INT identity(1,1) PRIMARY KEY, FirstName VARCHAR(max))
GO
CREATE INDEX IX_ wikitechyTableFirstName ON dbo. wikitechyTable (FirstName)
GO
Error Message :
- Msg 1919, Level 16, State 1, Line 1 Column ‘FirstName’ in table ‘dbo. wikitechyTable is of a type that is invalid for use as a key column in an index.
Data Storage :
Varchar(n): It uses the normal data pages to store the data i.e. it stores the value ‘in a row’.
Varchar(max):
- 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’.
- Upto 8k the data will be stored in the normal pages.
- Beyond it will be created and stored as TEXT data type and a pointer is placed to retrieve the data.
Applies to sqlserver version :
- SQL Server 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
Related Tags :
- Sql ServerDifference Between Varchar and Varchar(max)
