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 (http://blog.SQLAuthority.com)

About these ads

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

  1. Hi,

    I was trying to use this query and I’m getting an error stating incorrect syntax near MAX. Please help me out with this as I’m trying to pass some text into the database with more than 10000 chars.

    Thanks and Regards,

    Turya

    Like

  2. how can i change the table field order ???
    example for I have one table name XYZ and field are Code,Name,Address,Pin how I change the sequence like Name,Code,Address,Pin …. Please Help Me

    Shanku

    Like

  3. Hi Shanku,

    You can not do that. You will have to create new table with your desired column order and insert values from old table to new table. Drop the old table and rename the new table with the name of old table.

    However, the order of column does not matter to database.

    Regards,
    Pinal Dave(SQLAuthority.com)

    Like

  4. im having problem in sending query online to create a table with varchar(max)
    they say that the server is sql 2k5 and it seems like its 2005.

    but i have replaced max with 300.. cuz i dont really need too much details but what was wrong there?

    Like

  5. hello sir,
    i want to use MAX function in insert query, how can i do that?

    insert into xyz(id,name) values(max(id),’nirav dave’)

    i want to use max(id) in insert query coz i don’t want to fire special query to find max number of id,

    plz. help me.

    Like

  6. Hi !
    Can you help me please ?

    I had to change our database (not empty) collation from French_CI_AS to SQL_Latin1_General_CP850_CI_AI.

    : Database\Properties\Options\Collation. Change it !

    After doing this, i noticed that only data on fields with “varchar(max) ” type were truncated.
    Like, even if i put 100 characters by the interface, no problem but only 12 characters are really saved in the database. Troncation happens only on these types of fiels et always the same number of characters 12

    Can anyone knows why and how avoid this ?

    Thank you very much and sorry, i do not speak english very well :(

    Like

  7. Hi,

    I was trying to use this query and I’m getting an error stating incorrect syntax near MAX. Please help me out with this as I’m trying to pass some text into the database with more than 12000 chars.

    please send right qeury
    on my email id i need it very ugently

    Thanks and Regards,

    ajay

    Like

  8. Hello Sir
    my problem is: i enter the text in sql 2005. but some tile text cross the 8000 char which is max one.

    so i got error of data truncation

    how i solve this problem

    Like

  9. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  10. Hello,

    I need to declare a variable with Varchar of size 12000 in stored procedure. But in SQL Server it gives me 8000 only as the extreme limit. So how can I increase that upto 12000.

    help me please.

    Regards
    ~ riti

    Like

  11. Sir,

    I want to know the Maximum Sizes each of these Data Type can occupy(in terms of bytes)
    VARCHAR(MAX)
    NVARCHAR(MAX)
    NVARBINARY(MAX)
    IMAGE

    I am confused!!! can you please help me out ?

    Thanks

    Like

  12. i want to Store 12000 character string within single cell of column in table

    Error : The size (12000) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

    ———————————-

    Give me SQL server 2008 Query Only

    i am Builting Binary Tree for MLM .

    Thanks in Advance.

    not give me “GAPPA” answer

    amrut kanthavadiya

    Like

  13. Hi,

    I am using MSSQL-2008

    In my one of procedure im using dynamic Query and it is getting save in nvarchar(MAX)

    Here problem is that –

    My dynamic query is exceeding the length max limit and my dynamic query is not firing

    Is there any way-out to save LARGE queries in nvarchar

    Like

  14. 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.

    Like

        • 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??????

          Like

  15. 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,

    Like

  16. 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.

    Like

  17. 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

    Like

  18. 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..

    Like

  19. Pingback: SQL SERVER – Difference between CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) – Quiz – Puzzle – 15 of 31 « SQL Server Journey with SQL Authority

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

    Like

  21. 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?

    Like

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s