How to choose a data type when you build a table with SQL data types

ntext and text are used to hold large amounts of text data, but text saves data with a single byte, and ntext holds the data in two bytes. ntext holds uncode characters, ntext supports cross-language platforms.

ntext

The maximum length of a variable-length Unicode data is 230-1 (1,073,741,823) characters. The storage size is twice times the number of characters entered (in bytes). The synonym of ntext in SQL-92 is national text.

ntext The data is stored in double-byte, not show ntext you change the recordset to open the way you can.

Text
The maximum length of a variable-length non-Unicode data in a server code page is 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. The storage size may be less than 2,147,483,647 bytes (depending on the string).

The difference between char, varchar, text, and nchar, nvarchar, ntext

1, CHAR. Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, take up 10 bytes of space, insufficient to automatically fill with spaces.

2, VARCHAR. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

3, TEXT. Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.

4, NCHAR, NVARCHAR, NTEXT. These three kinds of names from the first three more than the previous "N". It represents a character stored in a Unicode data type. We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. The length of the nchar and nvarchar is between 1 and 4000. Compared to char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese characters, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. It can be seen that the use of nchar, nvarchar data types without worrying about the input characters are English or Chinese characters, more convenient, but in the storage of English number of some losses.


For when to use varchar and nvarchar did not say certain.
In other words, a Chinese character can exist in either varchar or nvarchar.
So what's the difference between a varchar and a nvarchar for Chinese or Unicode data?
The following example illustrates: A Chinese character occupies a varchar (2), only nvarchar (1), and the letter is only the varchar (1), then in the database field for the length of time, with varchar you do not necessarily know it exactly how many words, if using nvarchar , then the Chinese character is also nvarchar (1), the letter is also nvarchar (1), then it is already obvious.
The difference between 2:varchar's retrieval is faster than nvarchar, although this is the next version of Microsoft will be unified nvarchar, heard

Managing ntext, text, and image data

The ntext, text, and image data types of the Microsoft®sql Server™ can contain very large amounts of data in a single value (maximum
Up to 2 GB). A single data value is typically larger than the application can retrieve in one step, and some values may be larger than the client's available virtual memory. So
When retrieving these values, a few special steps are usually required.

If the ntext, text, and image data values do not exceed the length of a Unicode string, string, or binary string (4,000 characters, 8,000 words, respectively)
and 8, 000 bytes), they can be referenced in SELECT, UPDATE, and INSERT statements in the same way as smaller data types. Cases
For example, a ntext column that contains a short value can be referenced in the select list of a SELECT statement in the same way as a nvarchar column. Reference must comply with a
restrictions, such as ntext, text, or image columns that cannot be referenced directly in the WHERE clause. These columns can be returned as other data types (for example,
An argument to a function of ISNULL, SUBSTRING, or PATINDEX) is contained in a WHERE clause, or it can be contained in is NULL, was not NULL, or
The like expression.

Working with large data values
However, if the ntext, text, and image data values are large, they must be processed on a per-block basis. Both Transact-SQL and database APIs contain applications that enable the application to
A function that processes ntext, text, and image data by block.

The database API processes long ntext, text, and image columns in a common pattern:

To read a long column, the application simply includes the ntext, text, or image column in the select list and binds the column to a program variable
should be sufficient to accommodate the appropriate block of data. The application can then execute the statement and use an API function or method to retrieve the data into the bound variable in chunks.


To write a long column, the application can use the parameter marker (?) to replace the value in the ntext, text, or image column at the appropriate location to perform the INSERT
or an UPDATE statement. The parameter marker (or parameter to ADO) is bound to a program variable that is sufficient to hold the block of data. The application enters the loop,
The loop first moves the next set of data into the bound variable, and then calls the API function or method to write the data block. This process will be repeated until the entire data value
Send complete.
Use the text in row
In Microsoft SQL Server 2000, a user can enable the text in row option on a table so that the table can store text in its data rows,
ntext or image data.

To enable this option, execute the sp_tableoption stored procedure, specifying the text in row as the option name and specifying on as the option value. BLOB (binary
Large objects: text, ntext, or image data) The default maximum size that can be stored in a row is 256 bytes, but the value can range from 24 to 7000.
To specify a maximum size other than the default value, specify the integer in the range as the option value.

If the following conditions are applied, the text, ntext, or image string is stored in the data row:

Enable text in row.


The length of the string is shorter than the limit specified by the @OptionValue


There is enough free space in the data row.
When a BLOB string is stored in a data row, reading and writing the text, ntext, or image string can be associated with reading or writing a string and a binary string
As fast. SQL Server does not have to access a separate page to read or write to the BLOB string.

If the text, ntext, or image string is larger than the limit or free space specified in the row, the pointer is stored in that row. Storing BLOB characters in rows
The condition of the string still applies, however: there must be enough space in the data row to hold the pointer.

For more information, see sp_tableoption.

Using text pointers
If the text in row option is not specified, the text, ntext, or image string is stored outside the data row, and only the text pointers for these strings reside in the number
According to the line. The text pointer points to the root node of the tree generated by the internal pointer, and these internal pointers map to the actual storage (text, ntext, or image data)
The page of the string segment.

The text pointers in SQL Server 2000 are different from those in earlier versions of SQL Server. The behavior of the text pointer is like a file of BLOB data
The early text pointer function is like the address of the BLOB data. Therefore, when you use the row text pointer, remember the following features:



Important Although line literals are allowed in cursors, line text pointers are not allowed. If you attempt to declare a cursor that contains a row text pointer, SQL Server returns an error
Error messages (8654, 16, 1, "A cursor plan could not being generated for the given statement because it contains
TEXTPTR (Inrow LOB). ", 1033).

Digital
For each database, a maximum of 1024 active-line text pointers are allowed per transaction.

Lock
When the user obtains the active text pointer, SQL Server 2000 locks the data row when the first user controls the text pointer and ensures that no other user has modified or deleted
Except for that row. The lock is freed when the text pointer becomes invalid. To invalidate the text pointer, use SP_INVALIDATE_TEXTPTR.

Text pointers cannot be used to update BLOB values when the isolation level of a transaction is uncommitted or if the database is read-only mode.

SQL Server 2000 does not lock data rows when the database is in single-user mode.

For illustrative purposes, the following table is given:

CREATE TABLE T1 (c1 int, c2 text)
EXEC sp_tableoption ' t1 ', ' text in row ', ' on '
INSERT T1 VALUES (' 1 ', ' a ')

The following transactions will succeed:

INSERT T1 VALUES (' 1 ', ' This is text. ')
SET TRANSACTION Isolation Level READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr varbinary (16)
SELECT @ptr = textptr (C2)
from T1
WHERE C1 = 1
READTEXT t1.c2 @ptr 0 5
COMMIT TRAN
GO

The following transaction will fail:

SET TRANSACTION Isolation Level READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr varbinary (16)
SELECT @ptr = textptr (C2)
from T1
WHERE C1 = 1
WRITETEXT t1.c2 @ptr ' xx '
COMMIT TRAN
GO

Duration
The text pointer is valid only within a transaction. The text pointer becomes invalid when the transaction is committed.

Within a transaction, the text pointer may not be valid when any of the following actions occur:

Session ends.


Delete the data rows in the transaction. (Other transactions cannot delete a data row because the row contains locks.) ）


The schema of the table where the text pointer resides has changed. Schema change operations that invalidate text pointers include: Create or drop a clustered index, alter or drop a table, truncate a table,
Change the text in row option with sp_tableoption and execute sp_indexoption.
Using the previous example, the following scripts are valid in earlier versions of SQL Server, but errors are generated in SQL Server 2000.

DECLARE @ptrval varbinary (16)
PRINT ' Get error here '
SELECT @ptrval = TEXTPTR (C2)
from T1
WHERE C1 = 1
READTEXT t1.c2 @ptrval 0 1

In SQL Server 2000, you must use a row text pointer within a transaction:

BEGIN TRAN
DECLARE @ptrval varbinary (16)
SELECT @ptrval = TEXTPTR (C2)
from T1
WHERE C1 = 1
READTEXT t1.c2 @ptrval 0 1
COMMIT

NULL text
You can get the line text pointer on the NULL text generated by the INSERT. Previously, a text pointer could be obtained only if the BLOB was updated to NULL.

For example, the following code is not valid in SQL Server 7.0, but is valid in SQL Server 2000.

SET TRANSACTION Isolation Level READ COMMITTED
GO
INSERT into T1 VALUES (4, NULL)
BEGIN TRAN
DECLARE @ptrval VARBINARY (16)
SELECT @ptrval = TEXTPTR (C2)
from T1
WHERE C1 = 4
WRITETEXT t1.c2 @ptrval ' x4 '
COMMIT

In SQL Server 7.0, you must perform the following actions:

INSERT into T1 VALUES (4, NULL)
UPDATE T1
SET C2 = NULL
WHERE C1 = 4
DECLARE @ptrval VARBINARY (16)
SELECT @ptrval = TEXTPTR (C2)
from T1
WHERE C1 = 4
WRITETEXT t1.c2 @ptrval ' x4 '

The following table summarizes the differences.

Differential line text pointer non-line text pointer
number for each database, a maximum of 1024 active-line text pointers per transaction are allowed. Unlimited.
Locking keeps the data row S locked until the pointer becomes invalid.
Locks are not acquired when the transaction is read uncommitted or the database is single user or read-only mode.
Data rows are not locked.
The duration transaction or session end, delete row, or change the schema of the table is not valid. The delete row becomes invalid.
Null text is immediately available after inserting the null text. Only updates are available.


Using ntext, text, and image data through the database API
This section outlines how the database API handles ntext, text, and image data:

Ado
ADO can map a ntext, text, or an image column or parameter to a Field or Parameter object. Use the GetChunk method to retrieve data blocks
Write data block by chunk using the AppendChunk method. For more information, see Managing Long data types.

OLE DB
OLE DB supports the ntext, text, and image data types using the ISequentialStream interface. The ISequentialStream::Read method reads blocks by block
Long data, the ISequentialStream::Write method writes long data blocks to the database. For more information, see BLOBs and OLE objects.

Odbc
ODBC has a feature called "Data in execution" that can be used to work with ODBC data types for Long data: Sql_wlongvarchar (ntext),
Sql_longvarchar (text) and sql_longvarbinary (image). These data types are bound to a program variable. In this way, you can adjust
Use SQLGetData to retrieve long data chunk by block, and call SQLPutData to send Long data block by chunk. For more information, see Managing Text and image columns.

Db-library
The Db-library application also binds the ntext, text, and image columns to program variables. The Db-library function dbtxtptr is used to get a pointer to a number
The dbreadtext is used to read Long data chunk by block, according to a pointer to the position of the long column in the library. Letters like Dbwritetext, Dbupdatetext and Dbmoretext.
Number is used to write long data block by chunk.



Description does not support using Db-library to access line text.

How to choose a data type when you build a table with SQL data types