Difference between varchar and varchar(max)

Difference between varchar and varchar(max)

Hello Readers,

The purpose of this post is to make it clear what to use and when with respect to varchar data type in SQL Server.

Varchar(max) was introduced in SQL Server 2005 version. Few years back, I wasn’t clear about the Difference between varchar and varcharmax. It was always confusing for me which one to use.

Difference between varchar and varchar(max) Click To Tweet

 

Difference between varchar and varcharmax

Difference between varchar and varchar(max)

VARCHAR

  • Non-Unicode variable length character data.
  • varchar is a variable, you can assign a value to it, it can receive an int from 1 to 8000.
  • ExampleDECLARE @Name AS VARCHAR(20) = ‘DIPENDRA SHEKHAWATSELECT @Name
  • The maximum storage capacity for varchar is 8000 bytes.
  • You can create index on varchar column.
  • Can be used – If we know that data to be stored in the column or variable is less than or equal to 8000 characters.

VARCHAR(MAX)

  • Non-Unicode large variable length character data.
  • varchar(max) is a constant, it has a value of max.
  • ExampleDECLARE @Name AS VARCHAR(MAX) = ‘DIPENDRA SHEKHAWATSELECT @Name
  • The maximum storage capacity for varchar(max) is 2147483647 characters (2 GB).
  • You cannot create index on varchar(max) column.
  • Can be used – If we know that the data to be stored in the column or variable can cross a 8 Kilo Bytes Data page.

NOTE

In terms of performance there is not much Difference between varchar and varchar(max). varchar provides better performance results compared to varchar(max)

That’s all folks. Choose wisely on using varchar in SQL Server. Hope you found this article useful & worth reading.

What do you think?

Dear Reader,
If you have any questions or suggestions please feel free to email us or put your thoughts as comments below. We would love to hear from you. If you found this post or article useful then please share along with your friends and help them to learn.

Happy Learning!