Difference Between varchar(max) and varchar(8000)

1. Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters.
2. VARCHAR(MAX) uses the normal datapages until the content actually fills 8k of data as varchar(8000). When overflow happens, data is stored as old TEXT, IMAGE and a pointer is replacing the old content.
3. NVarchar(8000). Since varchar or NVarchar can only hold up to 8000 bytes, so the maximum length for NVarchar is 4000
4. VARCHAR(8000) can be indexed and stored in the MDF/NDF data file.But VARCHAR(MAX) can't be indexed because can reach high volume and then will be stored as a seperated file and not in the MDF/NDF data file.
5. Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index
6. VARCHAR(MAX) has some ambiguity, if the size of the cell is < 8000 chars, it will be treated as Row data. If it's greater, it will be treated as a LOB for storage purposes. You can know this by querying RBAR.