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.
Following command sets the option value to 500 bytes.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.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.
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.
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.
This is because, by default SQL Server stores 16 bytes pointer in a data row and actual data is stored on different data pages.