Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
 Posted - 2006-12-20 : 05:14:13
|
quote:
Using text in row to Store text, ntext, and image ValuesUsually, text, ntext, or image strings are large (a maximum of 2GB) character or binary strings 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 that map the pages in which the string fragments are stored.With Microsoft SQL Server, you can store small to medium text, ntext, and image values in a data row, thereby increasing the speed of queries accessing these values.When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.To store text, ntext, or image strings in the data row, enable the text in row option using the sp_tableoption stored procedure.sp_tableoption N'MyTable', 'text in row', 'ON'Optionally, you can specify a maximum limit, from 24 through 7000 bytes, for the length of a text, ntext, and image string stored in a data row: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: It is large enough to ensure 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.Although in general, you should not set the value below 72, you also should not set the value too high, especially for tables where most statements do not reference the text, ntext, and image columns or there are multiple text, ntext, and image columns. You can also use sp_tableoption to turn the option off by specifying an option value of either 'OFF' or 0:sp_tableoption N'MyTable', 'text in row', 'OFF'
Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-12-20 : 06:44:06
|
i've looked into it once but i haven't found it any better than varchar or varbinary.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-20 : 08:56:18
|
I did this a few days ago is a table where we needed to store an XML string. We needed to persist some application state data for a short time to be able to hand it off to another person for a different step of the application. It's a low volume application, and the data is only stored for a few minutes.The developers expect that the data will be able to fit in a varchar(4000), but no one could say for sure that it wouldn't be longer, so I used a TEXT column, and set it to:sp_tableoption N'MyTable', 'text in row', '7000' One thing I do not like about the way it is implemented is that when you generate a script of the table, it does not include this option.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-20 : 08:59:12
|
| So if you have a 28k string, there will be 4 records (automatically) in the table after insert?Peter LarssonHelsingborg, Sweden |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-20 : 09:11:28
|
quote:
Originally posted by Peso
So if you have a 28k string, there will be 4 records (automatically) in the table after insert?Peter LarssonHelsingborg, Sweden
SQL Server treats it like a regular TEXT column if it is over the 7000 character limit. In other words, a row in the main table that holds the other columns and the text pointer, and then the text stored in a linked list of 8K pages.To the application, it will appear as one row with a TEXT column. The SQL statements needed to use it are no different than if the option is not set. It is purely a physical storage option.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Have anyone used this technique?