SQL Server - Varchar Vs Varchar(Max) Data Type

Wikitechy | 523 Views | sql server | 08 Jun 2016

 

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 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Tags :

  • Differences,
  • Sql ServerDifference Between Varchar and Varchar(max)
  • Sql Server
  • VARCHAR
  • Varchar vs Varchar(MAX)
  • Varchar(Max)
  • Varchar(Max) Vs Varchar




Workshop

IOT Hackathon
Webinar

Join our Community

Advertise