Constraint to limit VARCHAR max character length in SQL Server

A business need may arise to limit a VARCHAR column to a certain length. Consider a scenario where in an application has a 10000 character limit for a column. The VARCHAR(max) is the suitable data type choice for this column. However a VARCHAR(max) column is not limited to 10000 characters. In that case we can use a constraint to limit VARCHAR(max) character length. An example is shown below

-- Add constraint to new table
CREATE TABLE tblCns
(
	Sno int identity,
	Col1 VARCHAR(MAX)
	CONSTRAINT chk_10000 CHECK (DATALENGTH([Col1]) <= 10000)
);

-- Add constraint to existing table
ALTER TABLE TblCns Add CONSTRAINT chk_10000_1 CHECK (DATALENGTH([Col1]) <= 10000)

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook