SA0053B : Don’t use deprecated TEXT,NTEXT and IMAGE data types

Deprecated NTEXT, TEXT, and IMAGE data types can lead to compatibility issues in SQL Server.

Description

In SQL Server, certain legacy data types such as ntext, text, and image are deprecated and will be removed in future releases. These data types were originally used for large text and binary data, but newer alternatives now provide better functionality and performance.

For example:

SQL
Copy
1-- Example using deprecated data types
2CREATE TABLE ExampleTable (
3    Description ntext,
4    ImageData image
5);

In this example, using ntext and image is discouraged because:

  • These data types are not supported in certain operations, limiting the flexibility of your SQL queries.

  • They lack compatibility with newer SQL Server features, impacting future scalability and maintainability of your databases.

How to fix

Replace outdated data types with modern alternatives to ensure compatibility and improve performance in SQL Server.

Follow these steps to address the issue:

  1. Identify any instances of deprecated data types such as ntext, text, and image in your database schema.

  2. Modify table definitions to replace ntext with nvarchar(max), text with varchar(max), and image with varbinary(max).

  3. Ensure that all stored procedures, functions, and application code are updated to reflect the new data type changes.

For example:

SQL
Copy
1-- Example of corrected table creation with modern data types
2CREATE TABLE ExampleTable (
3    Description nvarchar(max),
4    ImageData varbinary(max)
5);

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
1 hour per issue.
Categories

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE [dbo].[Person]
 2( 
 3    [Title]  [nvarchar]( 8 ) NULL
 4  , [FirstName]  [dbo].[Name] NOT NULL
 5  , [MiddleName]  [dbo].[Name] NULL
 6  , [LastName]  [dbo].[Name] NOT NULL
 7  , [Picture] IMAGE NULL                         -- varbinary(max) can be used instead image data type
 8  , [Description] TEXT NULL                 -- varchar(max) can be used instead image data type
 9  , [UnicodeDescription] [NTEXT] NULL -- nvarchar(max) can be used instead image data type
10);
11
12DECLARE  @desc AS TEXT                                 -- nvarchar(max) can be used instead image data type
Analysis Results
  Message Line Column
1 SA0053B : Consider using VARBINARY(MAX) instead of deprecated data type IMAGE. 7 14
2 SA0053B : Consider using VARCHAR(MAX) instead of deprecated data type TEXT. 8 18
3 SA0053B : Consider using NVARCHAR(MAX) instead of deprecated data type NTEXT. 9 25
4 SA0053B : Consider using VARCHAR(MAX) instead of deprecated data type TEXT. 12 18
See Also

Other Resources

Analysis Rules