Problem solve Get help with specific problems with your technologies, process and projects.

MAX vs. TEXT data types

Expert Adam Machanic discusses Microsoft's decision to deprecate the TEXT and IMAGE types, and instead move forward with the enhanced functionality of the MAX types.

Adam, I recently read your article Maximize large data with the new MAX data types. Instead of adding a new data type, it seems preferable to me that these new capabilities should just be added to an old text data type. The fact that they weren't implies that MAX data type is less efficient than text, and Microsoft didn't want to hurt performance of old text-based code. I have some of that code and would be willing to take a little performance hit to get MAX capability. If it's more than just a little performance hit, we should know that so we can make intelligent decisions.
There were many factors involved in Microsoft's decision to deprecate the TEXT and IMAGE types and instead move forward with the enhanced functionality of the MAX types. Most of the concern was not performance-related, but rather had to do with backward-compatibility. There is a lot of legacy code out there that makes use of these types, and breaking all of that code (and the applications that use it) was not something the SQL Server team felt comfortable doing.

I spoke with Christian Kleinerman, Program Manager for the SQL Engine team, about the decision to create the MAX data types instead of extending TEXT and IMAGE. He mentioned that the TEXT and IMAGE data types, "have many behaviors [the SQL Server team doesn't] quite like – the main one being the fact that physical addresses [are] exposed via TEXTPTR()."

For those who haven't experienced the absolute rapture of updating TEXT data, getting a pointer to the data using TEXTPTR() is one of the necessary steps for using the READTEXT and WRITETEXT functions. Those functions, unfortunately, are the only methods available for updating large documents stored in columns using the TEXT data type. And while these restrictions are the bane of developers everywhere, Kleinerman mentions that, "trying to improve some things in TEXT and IMAGE and at the same time deprecate some of [their] behaviors would have been a confusing proposition that would have likely resulted in yet another set of seemingly arbitrary restrictions – with backwards incompatibilities of course."

The SQL Server team, unfortunately, cannot work in a bubble. Customers will simply not stand for breakage to their legacy code, and Microsoft has certainly shown its commitment in the past for supporting even the most esoteric legacy software in order to keep customers happy. The TEXT and IMAGE data types are no different. Klinerman noted that the move to the MAX data types "would ensure backward compatibility" by not breaking TEXT and IMAGE – and give these less-than-loved types a "peaceful death" in the future!

Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.