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

May 26, 2007 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Server, SQL String, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Datatype | 6 Comments

6 Responses

on June 13, 2008 at 6:30 pm | Reply preethi

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.
on July 14, 2008 at 3:27 pm | Reply Amit jain

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
on October 25, 2009 at 8:26 pm | Reply Harish

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
on October 27, 2009 at 12:40 am | Reply Vinod

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
on November 9, 2009 at 6:13 pm | Reply ashjh

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
on November 9, 2009 at 7:29 pm | Reply Harish

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