Using text, ntext Data Type SQL Server

In sql server 2000 (or before ver) we uses Text,NText data type for storing very long length of string datatype. Text is suitable for non-unicode (1 byte) string and NText for unicode(2 bytes). we can't manipulate these datatype with the regular dmls, but it requires some other way because of it's storing process which is different from other data types (except Image which is stored in the same way for binary data). These datatypes's values are stored outside the row and a 16 bit pointer is tored which points to the root of the internal pointer which points the fragment of the data stored in different pages.
We need to use READTEXT (to read), WRITETEXT ( to replace ) and UPDATETEXT (to modify) these values.
First create a table and insert some values in it
CREATE TABLE TestNTextDataType ( [Id] [int] NULL , [Name] [Ntext] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into TestNTextDataType values(1,'This is a test')
insert into TestNTextDataType values(2,'This is second test')
Declare a pointer to the 'Name' Column as
Declare @ptrToCol varbinary(16)
Select @ptrToCol=TextPtr([Name]) from TestNTextDataType where [Id]=1
The @ptrToCol pointer points to the NText value 'This is a test'.

READTEXT { table.columnName @ptrToCol @offset @size } [ HOLDLOCK ]
Reads values from a column, starting from a specified offset and reading the specified number of bytes.
READTEXT TestNTextDataType.[Name] @ptrToCol 5 2
Result: is
The value of offset is 5 and as it is zero based it points to the 6th character 'i' and the length is 2 so the two characters starting from 'i' is returned. If you pass 0 as the size then 4KB of data is read.
Read More

WRITETEXT { table.columnName @ptrToCol } [ WITH LOG ] { data }
Permits minimally logged, interactive updating of an existing column. WRITETEXT overwrites any existing data in the column it affects
WRITETEXT TestNTextDataType.[Name] @ptrToCol 'Updated Test Final'
It will update the 'This is a test' value to the new value 'Updated Test Final'. Remember that the pointer @ptrToCol points to the [Name] column of the row with the id=1 (Where clause).
Read More

UPDATETEXTUPDATETEXT { table.columnName @ptrToCol } { NULL @insertOffset } { NULL @deleteLength } [ WITH LOG ] [ inserted_data]
Updates an existing field. Use UPDATETEXT to change only a part of a text, ntext, or image column in place.
UPDATETEXT TestNTextDataType.[Name] @ptrToCol 8 4 'New String'
Previous value was 'Updated Test Final' insert offset 8 points to 'T' of Test and the delete length 4 deletes the 'Test' and inserts the value 'New String'. So the result value becomes 'Updated New String Final'.
Read More

DataLength(Expression)
To get the length of the column value for Text,NText and Image datatype you have to use DataLength function.
Select DataLength([Name]) Length from TestNTextDataType where [Id]=1
will return 48 as the length of 'Updated New String Final' string because the datatype is NText of the column so every charter will take 2 byte and there are 24 characters. If the datatype was Text then it will return 24. The DATALENGTH of NULL is NULL.

PATINDEX , SET TEXTSIZE, SUBSTRING, TEXTVALID are use useful functions to work with these datatypes.
Note: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types.