Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type

Answer:

1) Varchar can store maximum 8000 Non-Unicode characters .Varchar(Max) can store maximum of 2 147 483 647 Non-Unicode characters i.e. maximum storage capacity is: 2GB.

2)We can create index on Varchar column data type.Index can’t be created on a Varchar(Max) data type columns.

3)Varchar uses the normal data pages to store the data i.e. it stores the value ‘in a row’.In case of 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’. 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.