Updating NTEXT,TEXT

The SQL Server's ntext, text, and image data types can hold huge amount of data (up to 2 GB) in a single value.

SQL stores information of TEXT,NTEXT as a pointer in a row of the table.The pointer(textptr) points to the actual data storage page.

SQL server allows to store TEXT,NTEXT in a datarow itself rather than storing pointer in the row. To achieve this we need to enable text in row option using sp_tableoption

Below is the command :

EXEC sp_tableoption 'tablename', 'text in row', 'ON' //Enables the 'text in row' option for table 'tablename'

EXEC sp_tableoption 'tablename', 'text in row', '2000' //Enables the 'text in row' option for table 'tablename', and set the limit to 2000

If the size of NTEXT,TEXT OR Image is greater than specified text in a row ,textptr will be stored in the row.

Example of updating NTEXT :

As NTEXT stores information as a pointer in a row we need a txtptr to update NTEXT data

I have created one table (ntextTbl) with Remarks and Id column

DECLARE @ptrval VARBINARY(16),@Result VARCHAR(8000)

SET @Result = 'Testing NTEXT'

SELECT @ptrval = TEXTPTR(Remarks)FROM ntextTbl WHERE id = 1

UPDATETEXT ntextTbl.Remarks @ptrval NULL NULL @result //This will append 'Testing NText' in existing Remarks row

WRITETEXT ntexttbl.Remarks @ptrval 'Replace complete NTEXT' //This will replace existing remarks row with 'Replace Complete NTEXT'


Happy Coding :)