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

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

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

    Like

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

    Like

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

    Thanks,
    Harish

    Like

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

    Thanks,
    Vinod

    Like

  5. 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,
    Ash

    Like

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

    Like

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

    Regards,
    Ganesan

    Like

    • Hi,

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

      Like

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

    Like

  9. 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.
    Thanks
    Chandan

    Like

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

    Like

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

    Like

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

    Select
    REPLACE(
    'Johns1'
    , '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.

    Like

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

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

    Like

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