On Thursday, 30 April 2015 13:30:42 UTC+1, Erland Sommarskog  wrote:   
   > Michael Cole (noone@invalid.com) writes:   
   > > This is one where there seems to be a lot of conflicting advice out   
   > > there in google-land.   
   > >   
   > > If we have tables with one or more wide columns - VarChar with size of   
   > > over 8K in total - should these columns be moved to their own   
   > > wide-column table with the initial table simply linking to them?  Is   
   > > there much of an advantage/disadvantage to this, or does it actually   
   > > depend on how much is stored in them, not the empty column widths?   
   >   
   > Yes, that's the answer: it depends.   
   >   
   > For instance, If I add an xml column to store some freer-form data, I may   
   > not want to store it out of table just because it could fit more than 8K of   
   > data.   
   >   
   > And if I have table where the main meat is the BLOB data, it is not likely   
   > that I would like to store it in a separate table.   
   >   
   > But it the blob is not often accessed and the table have lots of columns and   
   > I expect scans to be common place, storing it in a separte table makes   
   > sense.   
      
   Of course older SQL Server systems had different limitations,   
   and you can find older advice online.   
      
   But this may be a distraction; in a practical case,   
   you maybe can /do/ whatever is easiest in your database   
   design, and only consider how to make it perform most   
   efficiently if it turns out to be a problem.  After all,   
   you could use the time to thin about other parts of the   
   design, instead.  And the answer is still "it depends".   
      
   As of SQL Server 2005 (about to be retired, and maybe   
   me with it), you can declare varchar(max) which -   
   typically? - is stored in a separate page (LOB_DATA) but   
   usually can be treated just like varchar(9) in your SQL   
   statements - likewise nvarchar, varbinary - or formats   
   like varchar(999) which are stored either IN_ROW_DATA   
   if it'll fit, or ROW_OVERFLOW_DATA if not.   
      
   I think in at least one older product, you could define   
   row column sizes larger than 8060 bytes total but simply   
   weren't allowed to store a row that exceeded 8060.   
   Now you can (I don't remember if I'd noticed that before   
   today), but there is a technical impact on performance.   
      
   I think there /used/ to be an option to hold short   
   values of an unlimited-length column as IN_ROW_DATA -   
   maybe that was for [text] and [ntext] types?   
      
   You also can specify a table to hold its LOB_DATA   
   in a different file and filegroup (SQL Server uses   
   an an in-row pointer to find it, of course).   
   For performance, this is even closer to the result   
   of using a separate table to hold it.  LOB_DATA   
   cannot (unless this has changed) be compressed   
   for you.   
      
   And you /could/ create two tables for the data   
   and use a view that joins them, even to update   
   data - as long as you only update one table's   
   columns in one statement /or/ provide "INSTEAD OF"   
   triggers containing code to convert data change   
   statements (DML) on the view into operations on   
   each of the tables in it.  Although then any   
   other DBA knows that you got the design wrong   
   in the first place.  :-)   
      
   I don't remember if I ever did that, but maybe   
   even thinking about it is bad...   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)