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)

Solarwinds
, ,
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 am using

    DECLARE @Query AS VARCHAR(MAX)

    for maximum character. But Sp is going an error
    Msg 170, Level 15, State 1,
    Line 18: Incorrect syntax near ‘VARCHAR’.

    Please tell me what should i do for take maximum character.

    Reply
    • Which version of SQL Server are you using?

      Reply
      • SQL Server 2005

      • My SP is something like:

        ALTER PROC [dbo].[spEditorReport_Test]
        ( @PJID VARCHAR(20),
        @Pstartdate VARCHAR(20),
        @Penddate VARCHAR(20),
        @PEditorId VARCHAR(20),
        @IsDateAssigned BIT

        )
        AS
        BEGIN
        SET nocount ON
        DECLARE @jid VARCHAR(20),
        @StartDate VARCHAR(20),
        @endDate VARCHAR(20),
        @DBName VARCHAR(20),
        @Query VARCHAR(MAX)

        SET @DBName = ‘nature’
        SET @Query = ‘DECLARE @jid AS VARCHAR(20),
        @StartDate AS VARCHAR(50),
        @endDate AS VARCHAR(20),
        @DBName As NVARCHAR(20),

        ………………………………..
        ………………………………..
        (Having Big Length)
        ………………………………..
        ………………………………..

        print (@Query)
        exec (@Query)
        END

        Output: Only printing 8000 characters like –

        DECLARE @jid AS VARCHAR(20),
        @StartDate AS VARCHAR(20),
        @DBName As NVARCHAR(20),
        @PEditorId AS NVARCHAR(20),
        @IsDateAssigned BIT

        ……………. (Till 8000 char, rest it leaves)

        Please help me how can i print all characters???
        I have tired to try a lot to resolve this error??????

  • 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

Menu