SQL Server 2005 about data type Max

This article is an example of SQL Server 2005 solution to the maximum data type problem.

Things started very simply. Megaware's marketing department wants a new Web site to publish documents, and the development team feels that using a SQL Server 2000 database as a document storage repository makes things easier. Steve is Megaware's database administrator and doesn't see the big problem; storing documents in a database, rather than using a file system, means that the server needs to do more, but it also makes backup and management much easier. It should also be impossible for the database to become unsynchronized with the file system.

Many of the documents the marketing department wants to store are more than 8,000 bytes long, so obviously varchar is not the right type of data for this job. As an alternative, the text data type is used to define the fields where the data is stored. Because each text can accommodate 2GB of content, text to store the marketing department's colleagues throw into the database the largest file is no problem.

Months later, the market filled the entire database with a lot of boring copies. But that's not what Steve really cares about. The database is humming happily and everyone is satisfied with the results of the project.

Until the company's slogan changed that big day. The marketing team thinks "Megaware:it's really cool!" Sounds better than the original "It" Megaware ' s Way or the highway!. Because the marketing team has embedded the original banner in the footer of every document in the warehouse, Steve's job is to change the footer of all these documents.

"No problem," Steve thought, opening the SQL Server Query Analyzer tool to perform the following T-SQL batch:

UPDATE MarketingDocuments
SET Document =
REPLACE(Document,
'It''s MegaWare''s Way or the Highway!',
'MegaWare： It''s really cool!)

When he saw the error message, Steve's relaxed smile soon disappeared, "The parameter 1,text of the substitution function is invalid." ”

When the substitution function is written, it does not work on the text data type. It also does not work for charindex or substring-or at least they do not work in more than 8,000 characters. Further, developers forget to handle local variables of the text or image type, and do not actually support any operations. Even a simple update of a substring in a document requires obscure things, as well as READTEXT and WRITETEXT functions that are difficult to use. Rather than being a developer or a busy database administrator, it takes time to use different types of functions to figure out how to use them correctly.