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)
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.
Your question is not ver clear
Can you give us more informations?
How to Restore the database backup.bak file with different name in sql server 2005 without drop
original database.
You need to use WITH MOVE option
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.
Thanks,
Harish
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
What is the problem you are having?
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
@Vinod: you can try doing something like a casting
ex: @Critique = CAST(comments as varchar(max))
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 =
replace(convert(varchar(max),description),’oldstring’,’newstring’)
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
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).
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.
Thanks
Chandan
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.
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.
Make sure to read this post
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?
It is only possible if you access that from the front end application
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