One of the Jr. DBA at in my Team Member asked me question the other day when he was replacing TEXT field with VARCHAR(MAX) : How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowing 12500 characters.
Traditionally thinking we only create the field as long as we need. SQL Server 2005 does support VARCHAR(MAX) but does not support VARCHAR(12500). If we try to create database field with VARCHAR(12500) it gives following error.
Server: Msg 131, Level 15, State 3, Line 1
The size (12500) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).
The solution we came up was to put constraint on the VARCHAR(MAX). SQL SERVER 2005 supports VARCHAR(MAX) and Constraint.
CREATE TABLE [dbo].[MyTestTable] ( [VarChar12500] VARCHAR(MAX) )
GO
ALTER TABLE [dbo].[MyTestTable]
ADD CONSTRAINT [MaxLengthConstraint]
CHECK (DATALENGTH([VarChar12500]) <= 12500)
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
