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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “Constraint to limit VARCHAR max character length in SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *