SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

Yesterday, in Friday Afternoon team meeting. I was asked question by one of application developer

“I am asked in new coding standards to use VARHCAR(MAX) instead of TEXT. Is VARCHAR(MAX) big enough to store TEXT field?”

Well, I realize that I was not clear enough in my coding standard. It is extremely important for coding standards to be clear and have a enough explanation that developer have no doubt about them. I updated coding standards after the meeting. The answer is

“Yes, VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).”

There are more reasons to use VARHCAR(MAX) though this was verbal answer to technical question in our general meeting where the focus was “Web Application Architecture and SQL Server”.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Datatype, SQL String
Previous Post
SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key
Next Post
SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

Related Posts

25 Comments. Leave new

  • this is maintainded in one table ~v1~+~v2~ how i can add this ,and in another table value have been set for v1,v2 how i can add this.

  • Hello Pinal,

    Can you please tell the answer of a question. I am using sql 2005 i have more than 100 tables each table contain atleast one text/ntext column there is text. i want remove one specific text phrase.

    I want replace function of sql server. But I am not able to use that can i use these function with varchar(max)

    Please also provide me a solution for replace specific text from all table and all columns in a database

    Thanks in advance

  • Hi pinal,

    I have a issue here relelated to this. I am trying to access a “text” type and inserting that value into another table viw a stored procedure. I’ve tried to cast it, convert it, but nothing works.

    My code looks somethings like this:

    Declare @Critique varchar(max), @Feedback varchar(max)

    SELECT @Critique = CAST(comments as varchar(max)), @Feedback = CAST(public_critique as varchar(max)) FROM ASCO_vEXTERNAL_REVIEW_APPLICATIONS_LIST WHERE wf_task_assignment_id = @WfTaskAssignmentIDP1

    — comments and public_critique are defined as text in view (also tried with table) ASCO_vEXTERNAL_REVIEW_APPLICATIONS_LIST

    insert into WF_TASK_ASSIGNMENT_REVIEW (wf_task_assignment_review_id, wf_task_assignment_id, grantee_project_id, comments, public_critique) values (@NewID1, @WfTaskAssignmentIDP2, @GranteeProjectID, @Critique, @Feedback)

    Can you please help me with this as soon as possible. I would really appreciate this.


  • hi pinal,

    I’m having a hard time trying to convert nvarchar(max) to a string in my VB .NET application. Would you happen to know how I could fix this?


  • Hi,

    I am using a table for pasted resumes.

    Will setting the data type of this particular column as varchar(max) help in pasting the maximum number of characters in the column. As of now iam restricting it to varchar(8000).

    Are there any disadvantages of this in SQL Server 2005/2008.

    Thank you,

  • @Vinod: you can try doing something like a casting
    ex: @Critique = CAST(comments as varchar(max))

  • Hennie de Nooijer
    December 16, 2009 4:35 pm

    I’ve a question about varchar(max). We are loading information in a datawarehouse and i’m thinking of creating only varchar(max) fields because only the used space is stored + 2 bytes. Or am i missing something?

  • You guys are working way too hard…

    update mytable set description =

  • Hello Pinal,
    I am planning to replace a column with TEXT datatype to VARCHAR(max). Any reference of this column already has performance issues in our application. Will this conversion better the performance or will remain the same. From some blogs, I understand that if records have this column length less than 8000, SQLSERVER stores the data in side the row (based on option setting) and hence the performance will be better. Is it correct?
    Also we are storing only XML content in this column. If we change to XML type will the performance be better. Any other suggestions to improve the performance would be much helpful.


    • Marko Parkkola
      May 20, 2010 11:52 am


      You could try one thing before you change your column type. Create a new table for your XML data and add foreign key constraint pointing to the original table. Join this new table in your query instead of reading TEXT field from the original table.

      If you get this working properly with this new table you have some possibilities to optimize it even further (well, you have some these options with a single table also). You could for instance move this new table to a hard drive other that where your original table is. This could speed up the reads.

      But as with everything, testing and measurements (of the performance) are the magic words here.

      Now which data type should you choose… If you don’t need any actual XML functions, XPath, XML indexing or anything, I would go with VARCHAR(MAX).

  • I have a need to replace all email ids within an XML in a text column. The emails are different in each row and I want to do something like

    update Tablename set contents=replace(cast(contents as varchar(MAX)),’%’,’myemail@company.com’)

    * The “%” kind of represents all the email ids

    Is there anyway to do an update like this?

  • Hi Pinal I am facing a problem with IN operator in SQL2005. I have a column Airports ( ntext type)
    which has values like (‘Fra’,’lhr’,’ams’). I am using below Query as inner query .” Airport IN( Select cast(Airports as varchar(max)) from TestUserDifinedRegion where UdrName= ‘UDR4’ and UID = ‘1025’ )” but outer Query never gives result. If I run inner Query Separately and hard code returned result like this “Airport IN (‘Fra’,’lhr’,’ams’)” outer Query gives perfect result. Can u suggest me what mistake I am doing here.

  • Hi, I have a fields datatype that is a listed as nvarchar(max) and I want to push this data into a field with a datatype of image. How can I do this without losing data from the nvarchar(max) datatype field?

    If I try a straight insert I get the following error:

    Operand type clash: nvarchar(max) is incompatible with image

  • declare @mytext varchar(max)

    I read a file that has 800 rows in it and string the values together

    Row 1 ‘1112’
    Row 2 ‘8711’
    Row 800 ‘9817’

    I end up with ‘1112;/’8711;/9817’

    The problem is my string only holds up to Row 600 and I cannot get the entire rows to store in my @mytext.

    The column in table is text

    Any ideas.

  • Please anyone help me to solve below bug. I need to replace a character with a replacement text which has length more than 4000. Replace function works only if the replacement text is <= 4000 characters.

    Kindy reply.

    , '1'
    , Convert(nvarchar(max) ,
    REPLICATE( 'a' , 12000) ) — It will work only till 4000

    –Msg 8152, Level 16, State 10, Line 1
    –String or binary data would be truncated.

  • Hi,

    I have a PDF file which is stored in a database table in a column of image datatype. The PDF contains text as well as images. I just want to retrieve the text from the PDF. I am not bothered about the image. Is it possible to do this?

  • select * from a TableName where (cast(text as varchar2(25))) like ‘%’ +columnName+ ‘%’

  • the above query didn’t execute. How to write a query in which I can cast and match simultaneously?

  • Hello Pinal,
    I have a column with Varbinary(max) , i storing data and retrieving is fine until the same is placed under replication. I noticed the following
    Once the data is being replicated.
    i can place only small amount of data into this column. Why is this and how to over come the same

    Thanks in advance


Leave a Reply