{"id":738,"date":"2004-06-06T22:12:31","date_gmt":"2004-06-06T22:12:31","guid":{"rendered":"\/blogs\/kimberly\/post\/Text-in-Row-table-option-a-good-thing.aspx"},"modified":"2004-06-06T22:12:31","modified_gmt":"2004-06-06T22:12:31","slug":"text-in-row-table-option-a-good-thing","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/text-in-row-table-option-a-good-thing\/","title":{"rendered":"Text in Row table option – a good thing???"},"content":{"rendered":"

In a discussion with <\/FONT>Scott<\/FONT><\/A> about the “text in row” option for LOB (L<\/STRONG>arge Ob<\/STRONG>ject Data types: text, ntext, and image) data he questioned whether or not it was really a good idea to set… Well, I have mixed feelings about it really. In general I don’t recommend it unless EVERY query tends to want the LOB data and if the LOB data is in general – narrow (meaning very few bytes). Yes, you do skip the extra lookup into the text\/image b-tree but you also make all of the rows wider. If you don’t want the text info most of the time (i.e. the description is only needed when they [occassionally] click on that button for the request, etc.) then you’re putting all of those descriptions into memory when you don’t really need to. It’s [potentially] a great way to waste a large percentage of your cache.<\/FONT><\/DIV>
\n
<\/FONT> <\/DIV>
\n
So – I’d say that there really isn’t a byte limit more than a usage rule. BUT – if you do want to skip that lookup (because most of the time you DO want to return the description) then I’d start to look at the data and see what the “average” byte length is. If most of the descriptions are small but they want to use the text datatype for the very rare and very large description then this is a GREAT feature to keep the small (and very frequently <\/STRONG>accessed descriptions) with the data row and avoid the lookups!<\/FONT><\/DIV>
\n
<\/FONT> <\/DIV>
\n
(fyi – the b-tree index is just like any other; it’s the “index” associated with the table (object_id(‘tablename’) = id) and has an indid of 255 in sysindexes. Each table has only ONE btree for all LOB columns of that table and seeing the indexes, statistics, etc. that each table has is interesting if you ever look at the system tables.) <\/FONT><\/DIV><\/p>\n","protected":false},"excerpt":{"rendered":"

In a discussion with Scott about the “text in row” option for LOB (Large Object Data types: text, ntext, and image) data he questioned whether or not it was really a good idea to set… Well, I have mixed feelings about it really. In general I don’t recommend it unless EVERY query tends to want the […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[78],"tags":[],"class_list":["post-738","post","type-post","status-publish","format-standard","hentry","category-tips"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/738","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=738"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/738\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=738"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=738"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=738"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}