| title | ntext, text, and image (Transact-SQL) | |||||
|---|---|---|---|---|---|---|
| description | The ntext, text, and image data types are deprecated data types for storing large non-Unicode and Unicode character and binary data. | |||||
| author | MikeRayMSFT | |||||
| ms.author | mikeray | |||||
| ms.reviewer | randolphwest | |||||
| ms.date | 03/27/2025 | |||||
| ms.service | sql | |||||
| ms.subservice | t-sql | |||||
| ms.topic | reference | |||||
| ms.custom |
|
|||||
| f1_keywords |
|
|||||
| helpviewer_keywords |
|
|||||
| dev_langs |
|
|||||
| monikerRange | =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the Unicode UCS-2 character set.
Important
The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823). Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.
Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31 - 1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size might be less than 2,147,483,647 bytes.
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
The following functions and statements can be used with ntext, text, or image data.
| Functions | Statements |
|---|---|
| DATALENGTH | READTEXT |
| PATINDEX | SET TEXTSIZE |
| SUBSTRING | UPDATETEXT |
| Text and Image Functions - TEXTPTR | WRITETEXT |
| Text and Image Functions - TEXTVALID |
When you drop columns using the deprecated ntext data type, the cleanup of the deleted data occurs as a serialized operation on all rows. The cleanup can require a large amount of time. When you drop an ntext column in a table with lots of rows, update the ntext column to NULL value first, then drop the column. You can run this option with parallel operations and make it much faster.