SiteCrafting Blah Blah Blog
Oct. 27, 2006 at 11:25am
MS SQL Server 2005 text and ntext
text, ntext, and image data types have been deprecated
How I discovered that text, ntext, and image data types have been deprecated and replaced by varchar(max), ntext(max), and varbinary(max).
Recently, I needed to perform a query to update a number of links for the Pierce Count Library website. I thought I could use a simple REPLACE() string function, unfortunately, this turned out not to be the case. In order to update text and ntext datatypes using a query you're limited to a few functions, in my case I would be forced to use SUBSTRING() or UPDATETEXT().
My original plan was to update all links in a field using a query like this:
UPDATE web_pages SET
web_pages.article1 = REPLACE( web_pages.article1, "http://bad_link//", "http://")
Of course, when I tried this, I got an error that ntext datatypes are not allowed for the REPLACE function. So, I went online and begin searching for a function similar to REPLACE() that would work with ntext, that's when I found the msdn page for working with text, ntext, and image data types.
I could have figured out a way to use SUBSTRING() or UPDATETEXT(), but I determined that it would be much quicker to just look for the records that contained the bad links and update them manually.
Later, I decided to do some research on the subject, because I just couldn't believe that there wasn't a way to use simple string functions with text and ntext. That's when I came across an informit article about Date, Math and Text Functions in SQL Server 2000 that explains how string functions don't work with text and ntext.
Undaunted, I continued researching some more and that's when I found out that text, ntext, and image data types have been depracated for SQL 2005 and replaced by varchar(max), ntext(max), and varbinary(max). In fact, in future releases text, ntext, and image will no longer be supported. View the list at Deprecated Database Engine Features in SQL Server 2005.
If I had only known. I would have designed all the fields to use nvarchar(max) instead of ntext. This would have solved a couple of problems for me. The first problem is that ntext data types can't be used in GROUP BY clauses and the second I would have been able to update the bad links a whole lot quicker by being able to use string functions. And if there was a need, I would have been able to use string functions in select statements to manipulate the text.
Posted in ASP.NET 2.0, Coding Techniques, From the Workbench, MS SQL Server by Ken Foubert
Comments (1)
Good piece of info for everyone to know, Thanks
1 | Left by tony | Mar. 3, 2008 at 11:03pm