SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

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)

SQL Constraint and Keys, SQL Scripts, SQL String
Previous Post
SQL SERVER – Retrieve Information of SQL Server Agent Jobs
Next Post
SQLAuthority News – Dedicated Search Engine for SQLAuthority – Search SQL Solutions

Related Posts

50 Comments. Leave new

  • Hi,

    I’m Constructing a Dynamic Query in a Stored Procedure with Variable as

    DECLARE @Query VARCHAR(MAX)

    But when i try to print the constructed Query (Print @Query)
    it shows only around or less than 8000 chars, rest got truncated.

    Please provide me some solution.

    thanks,

    Reply
  • Hi,

    I really need some help~

    I am trying to use a batch file with fmt file to insert data into a table which I had declared as varchar (max), but somehow I keep encounter some problem as below:
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Invalid field size for datatype

    My BCP statement:
    bcp [Database].dbo.[table] in D:\20110815.ASC -fD:\fedline.fmt -F2 -Uuid -Ppwd -Ssvr -e ERROR.LOG

    this is my fmt file.
    7.0
    1
    1 SQLCHAR 0 14000 “\r\n” 1 data_str

    I am using SQL Server 2005, when I tried to change the fmt file to version 9.0 which is same as SQL Server 2005, I get the following error:
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file

    Could you please help me out? Thanks.

    Reply
  • Hi
    I am using SQL server 2005; i created a store procedure to put query result into XML into String.

    declare @returntext varchar(max)
    set @ReturnText = CAST ((
    select A, B, C, D from tableE for xml path , TYPE) AS VARCHAR(MAX))

    Total Length from the result should be 20000 characters; but it only return 8000 characters.

    Anything I can improve it and make it work.

    Thanks

    Reply
  • Carlos Mitani Sigala
    January 4, 2012 10:47 pm

    Great..!!!!! It worked for me… Thanks a lot my friend..

    Reply
  • the problem lies with some result output setting of SQL Server Management Studio..i run such queries on 3rd party database client – Toad for Data Analysts, it all worked well and displayed the complete string that exceeded 15000..

    Reply
  • hi i am new to sql server 2005 can we restrict the user login for certain table in sql

    Reply
  • thanks for the valuable guidance,this article helped me a lot.

    Reply
  • Can the ADD CONSTRAINT [MaxLengthConstraint]
    CHECK (DATALENGTH([VarChar12500]) <= 12500)
    be applied on a SQl Variable declared in a Storedprocedure?.

    Reply
  • I am really new to sql – I need to look at a database and determine the number of occurances of names with only 4 characters and then sort them in order.

    LEN and DATALENGTH keep telling me that they do not exist?

    Reply
  • how to set compatibility of the database ?

    Reply

Leave a Reply