Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Convert Oracle String Functions to SQL Server String Functions
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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)

Back to Tip of the Day List Next Tip