Change NTEXT Data Type to Fit More Data

Question

One of my coworkers is using SQL Server 2000 and is having a display issue where an XML column using data type ntext is displaying <Long Text>. I'm guessing that's because the character length is too short (16), but it will not allow any changes.

When trying to alter the data type to varchar a warning appears. Will changing the datatype from ntext to varchar/nvarchar affect the data in the table?

Answer 1

1. Changing to varchar will potentially lose Unicode information, e.g.

   DECLARE @x NVARCHAR(1) = NCHAR(257);
   
   SELECT @x, CONVERT(VARCHAR(1), @x);
   

   Results:

   ----  ----
   ā     a
   -- these are NOT the same!
   

2. Changing to nvarchar(<=4000) will potentially lose any data > 8000 bytes (4000 characters). Which may be okay if the designers chose ntext but never stored more than 4000 characters, but you should check that first:

   SELECT COUNT(*) FROM dbo.table WHERE DATALENGTH(next_column) > 8000;
   

   If this returns 0, you are probably safe to convert to NVARCHAR(4000), but you will still not have an easy time modifying long text data using the SSMS tools (that is not what they're for) and you still may have impacts to your applications

3. You may have applications or stored procedures that have special handling for ntext columns, for example they may rely on functions like READTEXT, WRITETEXT, UPDATETEXT and TEXTPTR. You'll want to search your codebase for these commands (here is one way to start looking at stored procedures that may contain these):

   SELECT o.name
     FROM sysobjects AS o
     INNER JOIN syscomments AS c
     ON o.id = c.id
     WHERE c.[text] LIKE '%READTEXT%'
        OR c.[text] LIKE '%WRITETEXT%'
        OR c.[text] LIKE '%UPDATETEXT%'
        OR c.[text] LIKE '%TEXTPTR%';
   

   Some chances there for false positives, hence "one way to start".

---

But if I were in your shoes I would not just change the type.

Also, the 16 is not the character length (I can assure you you can fit more than 16 characters in that column). text and ntext data is not stored on the data page (since it won't fit on a single page), and the 16 bytes is the number of bytes that represent the pointer so that, when the page is read, it can tell SQL Server where, off-row, to go find the actual data.

The real answer is to stop using the graphical tools in SSMS to look at (and especially to modify!) data. If you want to view the contents of an ntext column, you can select a substring, e.g.

SELECT CONVERT(NVARCHAR(4000), ntext_column) FROM dbo.table;

...or do this in an application. But don't use Open Table or EDIT TOP n ROWS as a spreadsheet.

Another real answer is to get off of SQL Server 2000 already. In modern versions of SQL Server there is a much friendlier data type called nvarchar(max).