ntext, text, and image (Transact-SQL)
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance
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.
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) bytes. 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 may 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.
|DATALENGTH (Transact-SQL)||READTEXT (Transact-SQL)|
|PATINDEX (Transact-SQL)||SET TEXTSIZE (Transact-SQL)|
|SUBSTRING (Transact-SQL)||UPDATETEXT (Transact-SQL)|
|TEXTPTR (Transact-SQL)||WRITETEXT (Transact-SQL)|
When dropping 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 dropping 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.
- Data Types (Transact-SQL)
- LIKE (Transact-SQL)
- SET @local_variable (Transact-SQL)
- Collation and Unicode Support
Submit and view feedback for