Eval Database Columns with ntext Data Types

The Comment columns in the Activity, Location, and PersonActivity tables have an ntext data type and can store 1,073,741,823 characters or 2 gigabytes (GB) of data. The default column length for an ntext column is 16 characters. You cannot change this value because the column contains a 16-byte pointer to the location of the ntext data and not the data itself. The actual ntext value is not stored as part of the data; it is stored in a separate collection of data pages. The ntext data type (the Unicode text data type) has the following characteristics, which might affect your decision to specify this data type for a column:

• An ntext column cannot be used in an ORDER BY clause. Because the Eval columns with ntext data types contain comments and notes, which by nature are more likely to be sentences than single words, no benefits are lost because you cannot sort on this column.
• An ntext column with a large data value must be handled on a block-by-block basis. The database designers do not expect ntext columns in the Eval database to be more than 4,000 characters long and therefore the columns do not require special handling. Your application can use database APIs to read and write columns in a block-by-block fashion when ntext data values are long.
• The ntext data type cannot be used for variables or parameters in stored procedures. The content of the Comment fields in the Eval database makes it unlikely that they will be used for variables or parameters, and this restriction does not affect the stored prodedures used by the PT application.

In addition, it is a good idea to define the table column that has an ntext data type as the last column in the table because any SELECT statement that retrieves this column requires that this column be last. When a table has more than one ntext field, place the ntext fields last. You can explicitly specify the order of the columns in the SELECT statement, regardless of the position of the ntext field in the column definitions. Converting a Recordset to XML in the Implementation section describes reading ntext data.