VARCHAR(MAX) data type was introduced in SQL Server 2005. Prior to having VARCHAR(MAX), in SQL Server 2000, the maximum length that can be specified for the VARCHAR data type is 8000. The maximum length for a column that is defined as VARCHAR(MAX) data type is 2,147,483,645 characters.
Assuming you need to define a VARCHAR column that has a maximum of 10,000 characters. Defining the column as VARCHAR(10000) will generate the following error message:
Msg 131, Level 15, State 3, Line 1
The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
To avoid this error as well as to accomplish the requirement of having a maximum of 10,000 characters for a column, what you can do is to define the column as VARCHAR(MAX). Then to make sure that a maximum of 10,000 characters only are stored in this column, a CHECK constraint needs to be created against the column that checks the length of the value being stored in the column.
To illustrate, assuming you have a table called [dbo].[Company] that contains a column called [CompanyProfile] defined as VARCHAR(MAX). To limit the length of this column to 10,000 characters, the following constraint needs to be created:
ALTER TABLE [dbo].[Company]
ADD CONSTRAINT [MaxLength10000]
CHECK (DATALENGTH([CompanyProfile]) <= 10000)