SQL Server ntext, text, and image

It is used to store the fixed-length data types and variable-length data types of large non-Unicode, Unicode, and binary data. Unicode data uses the UNICODE UCS-2 character set.

 

An example in actual work:

    

1 DECLARE @ ptrval varbinary (16)
2 SELECT @ ptrval = TEXTPTR (f006l_0046)
3 FROM TB_TEXT_0046 where ob_textid_0046 = '000000'
4 READTEXT TB_TEXT_0046.f006l_0046 @ ptrval 0 3295
5 GO

 
When we look at these types of data in the query analyzer or Enterprise Manager, we usually cannot see or see endless data. Actually there is a ReadText function in the T-SQL to do this, if you do not know the length of the current column, a silly way is to set a very large, then, when you run the command, an error message is displayed. The maximum value is displayed in the error prompt.

The following text is taken from Ms SQL2000 books online.

    
 

READTEXT
Reads the value of text, ntext, or image in the text, ntext, or image column, and reads the specified number of bytes starting from the specified offset.

Syntax
READTEXT {table. column text_ptr offset size} [HOLDLOCK]

Parameters
Table. column

Is the name of the table and column read from. The table name and column name must comply with the identifier rules. The table name and column name must be specified, but you can choose whether to specify the database tutorial name and owner name.

Text_ptr

Valid text pointer. Text_ptr must be binary (16 ).

Offset

The number of bytes skipped before reading text, image, or ntext data (when the text or image data type is used) or the number of characters (when the ntext data type is used ). When the ntext data type is used, offset is the number of characters that are skipped before reading data. When the text or image data type is used, offset is the number of bytes that are skipped before reading data.

Size

Is the number of bytes (when the text or image data type is used) or the number of characters (when the ntext data type is used) of the data to be read ). If the size is 0, 4 KB of data is read.

HOLDLOCK

Lock the text value until the transaction ends. Other users can read the value but cannot modify it.

Note
Use the TEXTPTR function to obtain valid text_ptr values. If more than one row is returned, TEXTPTR returns a pointer to the text, ntext, or image column in the specified row, or a pointer to the text, ntext, or image column in the last row returned by the query. Because TEXTPTR returns a 16-byte binary string, it is best to declare a local variable that controls the text pointer and then use this variable in READTEXT. For more information about declaring local variables, see declare.

Invalid text pointers may exist in SQL Server 2000. For more information about the text in row option, see sp_tableoption. For more information about how to invalidate a text pointer, see sp_invalidate_textptr.

If the value of the @ TEXTSIZE function is smaller than the size specified by READTEXT, it replaces the size specified by READTEXT. @ TEXTSIZE refers to the limit on the number of bytes returned by the set textsize statement. For more information about how to set textsize session settings, see set textsize.

Permission
By default, READTEXT permission is granted to users who have SELECT permission on the specified table. These permissions can be passed when the SELECT permission is passed.

Example
The following example reads 2nd to 26th characters from the pr_info column in The pub_info table.

USE pubs
GO
DECLARE @ ptrval varbinary (16)
SELECT @ ptrval = TEXTPTR (pr_info)
FROM pub_info pr inner join publishers p
ON pr. pub_id = p. pub_id
AND p. pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ ptrval 1 25
GO

 

Ntext
A variable-length Unicode data. The maximum length is 2 ^ 30-1 (1,073,741,823) characters. The storage size is twice the number of input characters (in bytes ). The ISO synonym for ntext is national text.

Text
Variable-length non-Unicode data in the server code page. The maximum length is 2 ^ 31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. According to the string, the storage size may be less than 2,147,483,647 bytes.

Image
Variable binary data, ranging from 0 to 2 ^ 31-1 (2,147,483,647) bytes.

Note
--------------------------------------------------------------------------------

The following functions and statements can be used with ntext, text, or image data.