SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated

Introduction

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:

-- Example of deprecated usage
WRITETEXT 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:

-- Example of corrected query using UPDATE with .WRITE
UPDATE TableName
SET ColumnName.WRITE('newData', @Offset, @Length)
WHERE 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.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
   AND p.pub_name = 'New Moon Books'
WRITETEXT 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!';
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b';
READTEXT pub_info.pr_info @ptrval 1 25;

UPDATE pub_info SET pr_info .WRITE (N'test',28,10)

Analysis Results

	Message	Line
1	SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated.	6
2	SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated.	7
3	SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated.	8

Analysis Rules