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

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?

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.

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

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?

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.

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?

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.