Limit a VarChar Column to 10,000 Characters

Before SQL Server 2005 then maximum length of a VarChar data type is 8000. In SQL Server 2005 VarChar(Max) data type was introduced. VarChar(Max) can store upto 2,147,483,645 characters. 


Now you want to restrict the length of a VarChar data type to 1000. You had defined the column as VarChar(10000) and upon saving you got an error:

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).

Now it means you can set length of a column to either MAX or <= 8000. So follow below procedure to define max length to 10000.

To do this you had to create a check constraint against that column and set data type to Varchar(Max).

Example: added a column Address in CustomerMaster Table with Data type Varchar(Max). Now add a contraint on address column to restrict input length to 10000.

ALTER TABLE CustomerMaster ADD CONSTRAINT [MaxLen10000] CHECK (DATALENGTH([Address]) <= 10000)