| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ntext, text, and image (Transact-SQL) |
The ntext, text, and image data types are deprecated data types for storing large non-Unicode and Unicode character and binary data. |
MikeRayMSFT |
mikeray |
randolphwest |
03/27/2025 |
sql |
t-sql |
reference |
|
|
|
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!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.