Re: blob's vs varchar

SaTriGuy wrote: > > >I am wondering about anyones opinion on the best choice for > >a comment field in a table > > > >The comment could be from a sentence to pages and pages long > > > >our DBA would like to implement a varchar 256 for short comments > >and only have a blob for comments over 256 chars > > The blob will require 56 bytes of header information even if it is null. > I'd go with the text fields only. > > However, there is a performance hit with text columns, as the text column > does not reside on the same page as the rest of the data. Thus an > aditional IO will be required if the text column is selected. Personally, I'd go for a fixed char column holding one line or one page of commentary and multiple rows as needed with a sequence number. I find a) This is REALLY easy to implement and program for as each fetch will match a display element (ie line/page etc). b) Minimizes waste (you can do better here with varchar column and line granularity but since the vast majority of commentary lines will tend toward a full line you do not save much. Also, varchar rows may need to be moved to another page, other than their home page, even if they are smaller that the page size, and this also incurs a second I/O to follow the link in the slot table of the home page to the new location.) Do not fall into the trap of thinking 'fixed char wastes half on average'. In your example you will have one or more full, or nearly full, lines and a few nearly empty lines at the ends of paragraphs (with line granularity). With page granularity you need to determine the most likely comment size and set the "page" size accordingly to avoid waste. Art S. Kagel