SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated

Deprecated textpointer statements in SQL Server may be removed in future versions, potentially causing compatibility issues.

Description

In SQL Server, certain statements such as WRITETEXT, UPDATETEXT, and READTEXT are deemed deprecated. These commands involve the manipulation of text, ntext, and image data types using a pointer-based approach, which is discouraged in current best practices.

For example:

SQL
Copy
1-- Example of deprecated usage
2WRITETEXT tableName.textCol @textPointer newData;

Using WRITETEXT in this manner is problematic because it relies on text pointers that are not optimized for modern SQL Server versions, potentially leading to maintenance complications.

  • These methods are performance inhibitors, as they bypass SQL Server’s built-in data manipulation capabilities.

  • As SQL Server evolves, reliance on deprecated features can increase the risk of future compatibility issues.

How to fix

This guide explains how to replace deprecated textpointer statements in SQL Server to ensure compatibility with future versions and improve performance.

Follow these steps to address the issue:

  1. Identify instances of deprecated commands like WRITETEXT, UPDATETEXT, and READTEXT within your database scripts.

  2. Replace these deprecated commands by transitioning to large-value data types such as VARCHAR(MAX) or NVARCHAR(MAX).

  3. Use the .WRITE clause of the UPDATE statement to manipulate large text data. This method ensures better alignment with SQL Server’s built-in data handling capabilities.

  4. Test the revised queries to ensure they maintain the intended functionality and performance.

For example:

SQL
Copy
1-- Example of corrected query using UPDATE with .WRITE
2UPDATE TableName
3SET ColumnName.WRITE('newData', @Offset, @Length)
4WHERE Condition;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1DECLARE @ptrval binary(16);  
 2SELECT @ptrval = TEXTPTR(pr_info)   
 3FROM pub_info pr, publishers p  
 4WHERE p.pub_id = pr.pub_id   
 5   AND p.pub_name = 'New Moon Books'  
 6WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'; 
 7UPDATETEXT pub_info.pr_info @ptrval 88 1 'b';  
 8READTEXT pub_info.pr_info @ptrval 1 25;  
 9
10UPDATE pub_info SET pr_info .WRITE (N'test',28,10)
Analysis Results
  Message Line Column
1 SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated. 6 0
2 SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated. 7 0
3 SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated. 8 0
See Also

Other Resources

Analysis Rules