sql server 中nvarchar(max)性能

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

Sometimes you want the data type to enforce some sense on the data in it.

Say for example you have a column that really shouldn't be longer than, say, 20 characters. If you define that column as VARCHAR(MAX), some rogue application could insert a long string into it and you'd never know, or have any way of preventing it.

The next time your application uses that string, under the assumption that the length of the string is modest and reasonable for the domain it represents, you will experience an unpredictable and confusing result.

There are a few diferences between VARCHAR(1-8000) and VARCHAR(MAX).

when you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of  row'. It means that the data row will have a pointer to another location where the 'large value' is stored. By default sql server will try to accomodate the value 'in row' but if it could not, it will store the large values 'out of row'. When values are stored 'out of row' there will be slight processing overhead in reading the information. Here is a good reference:http://msdn2.microsoft.com/en-us/library/ms189087.aspx

I guess you cannot index a VARCHAR(MAX)/NVARCHAR(MAX) column.

coming back to your question:

I dont think it is bad to use VARCHAR(MAX) is bad. If you are storing smaller piecs of data in a VARCHAR(MAX) column, it will be treated as normal. If you dont want to index the column, then you can definitely go with VARCHAR(MAX) option.

But most people do not advise that. First of all, by having a VARCHAR(MAX) will confuse some one who looks at the data later on. For example, if you want to store a comment of 100 characters or address of 80 characters, why should you go for VARCHAR(MAX)? If you use Address VARCHAR(MAX), comments VARCHAR(MAX), Name VARCHAR(MAX), some one trying to read or write data on a later date will be confused. They will not know what is the expected size of the data and they will be compelled to use LARGE VALUE types always.

Small-to-medium large value types (varchar(max), nvarchar(max), varbinary(max), and xml) and large object (LOB) data types (text, ntext, and image) can be stored in a data row. This behavior is controlled by using two options in the sp_tableoption system stored procedure: the large value types out of row option for large value types, and the text in row option for large object types. These options are best used for tables in which the data values of any one of these data types are typically read or written in one unit, and most statements that reference the table refer to this kind of data. Depending on usage or workload characteristics, storing data in-row may not be useful.

Unless the text in row option is set to ON or to a specific in-row limit, text, ntext, or image strings are large character or binary strings (up to 2 gigabytes) that are stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers. These pointers map the pages in which the string fragments are stored. For more information about the storage of text, ntext, or image strings, see Using text and image Data.

You can set a text in row option for tables that contain LOB data type columns. You can also specify a text in row option limit, from 24 through 7,000 bytes.

Similarly, unless the large value types out of row option is set to ON, varchar(max), nvarchar(max), varbinary(max), and xml columns are stored, if it is possible, inside the data row. If this is the case, the SQL Server Database Engine tries to fit the specific value if it can, and will push the value off-row otherwise. If large value types out of row is set to ON, the values are stored off-row and only a 16-byte text pointer is stored in the record.

When a table is configured to store either large value types or large object data types directly in the data row, the actual column values will be in-row if either of the following conditions exist:

• The length of the string is shorter than the specified limit for text, ntext, and image
• There is sufficient space available in the data row to hold the string.

When a large value type or a large object data type column value is stored in the data row, the Database Engine does not have to access a separate page or set of pages to read or write the character or binary string. This makes reading and writing the in-row strings about as fast as reading or writing limited size varchar, nvarchar, or varbinary

For large object data types, if the string is longer than the text in row option limit or the available space in the row, the set of pointers that are otherwise stored in the root node of the pointer tree are stored in the row. The pointers are stored in the row if either of the following conditions exist:

• The amount of space needed to store the pointers is shorter than the specified text in row option limit.
• There is sufficient space available in the data row to hold the pointers.

When pointers are moved from the root node to the row itself, the Database Engine does not have to use a root node. This can eliminate a page access when reading or writing the string. This improves performance.

When root nodes are used, they are stored as one of the string fragments in a LOB page and can contain up to five internal pointers. The Database Engine needs 72 bytes of space in the row to store five pointers for an in-row string. If there is insufficient space in the row to hold the pointers when the text in row option is ON or the large value types out of row option is OFF, the Database Engine may have to allocate an 8-K page to hold them. If the data length of the value exceeds 40,200 bytes, more than five in-row pointers are required, at which point only 24 bytes are stored in the main row and an additional data page is allocated on the LOB storage space.

When large strings are stored in the row, they are stored similarly to variable-length strings. The Database Engine sorts columns in decreasing size order and pushes values off-row until the remaining columns fit in the data page (8K).

You can enable the large value types out of row option for a table by using sp_tableoption in the following way:

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

If you specify OFF, the in-row limit for varchar(max), nvarchar(max), varbinary(max), and xml

With the value of this option set to OFF, many strings may end up stored in the row itself, potentially reducing the number of data rows that fit on each page. If most statements that reference the table do not access the varchar(max), nvarchar(max), varbinary(max), and xml

You can also use sp_tableoption to disable the out-of-row option:

sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

When the large value types out of row option value is changed, existing varchar(max), nvarchar(max), varbinary(max), and xml

To examine the value of the large value types out of row option for a specific table, query the large_value_types_out_of_row column of the sys.tables catalog view. This column is 0 if the table does not have large value types out of row enabled, and 1 if large value types are stored out of row.

You can enable the text in row option for a table by using sp_tableoption in the following way:

sp_tableoption N'MyTable', 'text in row', 'ON'

Optionally, you can specify a maximum limit, from 24 through 7,000 bytes, for the length of a text, ntext, and image

sp_tableoption N'MyTable', 'text in row', '1000'

If you specify ON instead of a specific limit, the limit defaults to 256 bytes. This default value provides most of the performance benefits that can be gained by using the text in row option. Although you generally should not set the value lower than 72, you also should not set the value too high. This especially applies for tables in which most statements do not reference the text, ntext, and image columns; or in which there are multiple text, ntext, and image

If you set a large text in row limit, and many strings are stored in the row itself, you can significantly reduce the number of data rows that fit on each page. If most statements that reference the table do not access the text, ntext, or image columns, decreasing the rows in a page can increase the pages that must be read to process queries. Reducing the rows per page can increase the size of indexes and the pages that might have to be scanned if the optimizer finds no usable index. The default value of 256 for the text in row limit is large enough to make sure that small strings and the root text pointers can be stored in the rows, but not so large that it decreases the rows per page enough to affect performance.

The text in row option is automatically set to 256 for variables with a table data type and for tables returned by user-defined functions that return a table. This setting cannot be changed.

You can also use sp_tableoption to disable the option by specifying an option value of either OFF or 0:

sp_tableoption N'MyTable', 'text in row', 'OFF'

To examine the value of the text in row option for a specific table, query the text_in_row_limit column of the sys.tables catalog view. This column is 0 if the table does not have text in row enabled, and a value greater than 0 if the in-row limit has been set.

The text in row option has the following effects:

• After you have enabled the text in row option, you can use the TEXTPTR, READTEXT, UPDATETEXT or WRITETEXT statements to read or modify parts of any text, ntext, or image value stored in the table. In SELECT statements you can read the whole text, ntext, or image string, or use the SUBSTRING function to read parts of the string. All INSERT or UPDATE statements that reference the table must specify complete strings and cannot modify only a part of a text, ntext, or image
• When the text in row option is first enabled, existing text, ntext, or image strings are not immediately converted to in-row strings. The strings are converted to in-row strings only if they are subsequently updated. Any text, ntext, or image string inserted after the text in row option is enabled is inserted as an in-row string.
• Disabling the text in row option can be a long-running, logged operation. The table is locked and all in-row text, ntext, and image strings are converted to regular text, ntext, and image strings. The length of time the command must run and the amount of data modified depends on how many text, ntext, and image
• The text in row option does not affect the operation of the SQL Server Native Client OLE DB Provider or the SQL Server Native Client ODBC driver, other than to speed access to the text, ntext, and image
• The DB-Library text and image functions, such as dbreadtext and dbwritetext, cannot be used on a table after the text in row option has been enabled.