SA0053B : Don’t use deprecated TEXT,NTEXT and IMAGE data types
Introduction
Section titled “Introduction”Deprecated NTEXT , TEXT , and IMAGE data types can lead to compatibility issues in SQL Server.
Description
Section titled “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:
-- Example using deprecated data typesCREATE TABLE ExampleTable ( Description ntext, ImageData image);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
Section titled “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:
-- Example of corrected table creation with modern data typesCREATE TABLE ExampleTable ( Description nvarchar(max), ImageData varbinary(max));The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Design Rules, Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”CREATE TABLE [dbo].[Person]( [Title] [nvarchar]( 8 ) NULL , [FirstName] [dbo].[Name] NOT NULL , [MiddleName] [dbo].[Name] NULL , [LastName] [dbo].[Name] NOT NULL , [Picture] IMAGE NULL -- varbinary(max) can be used instead image data type , [Description] TEXT NULL -- varchar(max) can be used instead image data type , [UnicodeDescription] [NTEXT] NULL -- nvarchar(max) can be used instead image data type);
DECLARE @desc AS TEXT -- nvarchar(max) can be used instead image data typeAnalysis Results
Section titled “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 |