Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for August 3rd, 2007

TEXT in ROW option in SQL Server

Posted by decipherinfosys on August 3, 2007

MS SQL Server allows us to store large variable length data in text, ntext or image columns. One can store up to 2GB worth of non-Unicode character or binary data in text columns.  Sometimes querying text data from a table with lots of rows may result in performance degradation. Query retrieving text data takes longer than their counter parts (CHAR/ VARCHAR) columns. The performance may become sluggish further down the line when data volume increases in the table. MS SQL Server provides a way to circumvent this problem to an extent. In this blog post, we will show how we can achieve this.

SQL Server stores text data on separate pages but it stores 16 byte pointer value in the data row. Let us start with creating a table first for this post.


Also run sp_help stored procedure to see table properties. Following is the abbreviated output.

Column_name    Type    Length
———–    —-    ——
TEST_ID    int    4
TEST_DESC   text  16

Output of sp_help shows that even though TEST_DESC column can contain very large data, length of the column is 16 bytes. This is because, by default SQL Server stores 16 bytes pointer in a data row and actual data is stored on different data pages. If you execute sp_help for ‘TEXT’ data type, and examine length and precision values it will become clear.

sp_Help text

Type_name    Storage_type    Length    Precision
———    ————    ——    ———–
text        text               16        2147483647

Internally querying text column is a two step process assuming we are selecting TEST_ID and TEST_DESC columns from the table.
•    In first step, text pointer will be obtained from a data row when query is executed.
•    In second step, actual text data will be fetched using pointer value obtained in the first step.
This takes toll on performance specially, when we are working on large data sets.

To mitigate this issue, SQL Server has provided ‘text in row’ table option. The option value can be set to ‘ON’ or to specific value (between 24 and 7000) using SP_Tableoption system stored procedure. As name suggests, it sets the specific options at table level. For all the available options which can be set using SP_Tableoption please refer to BOL.

By default option value for ‘text in row’ option is set to off. When value is set to ‘ON’, it stores first 256 bytes of text data in the data row it self. When value is set to specific integer, let’s say 500, it stores up to first 500 bytes in the data row it self. This way when we select data from text column, it data length of records is less than or equal to 500 bytes, SQL Server can get data right from the data row saving a trip to grab the actual text from different data page and thus it will boost the performance.  Ideal number will be average data length of the text column which can vary for each application. Following command sets the option value to 500 bytes.

EXEC sp_tableoption ‘TEST’, ‘text in row’, ‘500’

Let us execute sp_help procedure again to see the effect of the option on table properties.

sp_Help test

Column_name    Type    Length
———–    —-    ——
TEST_ID    int    4
TEST_DESC   text  500

Setting this option will not affect existing data. But any rows inserted or updated after option is set, text data will be placed in a data row if it meets following criteria otherwise data pointer will be stored in a data row and actual data will be stored on different data pages.

•    The length of the text column is shorter than the option value specified. In our case, it is 500 bytes.
•    There is enough space available in the data row.

In order to accommodate existing rows we need to either move data to some other table, drop and re-create the original table and move data back to original table or more easier by performing fake update (updating the rows with the same value) eg.


One thing to remember though, if ‘text in row’ option is enabled on the table and application uses READTEXT, WRITETEXT or UPDATETEXT commands to manipulate the text column, it should be done under active transaction (Between BEGIN TRAN .. END TRAN) otherwise it will display following error.

Msg 7101, Level 16, State 1, Line 2
You need an active user transaction in order to use text pointers for a table with the option “text in row” set to ON.

Disabling ‘text in row’ option is a logged operation. It also locks table while converting in-row text data to regular text data. It can also run for a long time depending upon the volume of data it needs to convert. Turning this option on or off should be done with extra care and its performance should be measured before it is implemented in production databases.

As per BOL, ‘text in row’ option will be phased out from the future versions. Instead Microsoft is suggesting use of ‘large value types out of row option’ for large data types. There are new large data types in SQL Server 2005 varchar(max)/ nvarchar(max)/ varbinary(max) which can be used instead of text/ ntext/ image data types.

Unlike ‘text in row’ option, when ‘large value types out of row option’ is set to ON, values are stored off the row into different data pages. Only 16 byte text pointer is set into the data row.  When this option is set to OFF, SQL Server tries to fit the value in data row and if value does not fit, it pushes off to the different page storing 16 byte text pointer.

Posted in SQL Server | Leave a Comment »