title: Do i have to store large value data types in separate table?
body: <p>Question is old and i guess has no 100% right answer. But would like to hear more experienced advice. </p>
  
  <p>Using SQL Server 2008 R2.</p>
  
  <p>I have table where will be stored millions of rows. Most of the columns is description (date, status, title, ..) of varbinary(max) column data. There are also 2 columns of XML data type. these XML's are small and will be queried often. So:</p>
  
  <pre><code>   MyTable
     (
          SomeID varchar(20)-- queried most often
          Date DateTime -- queried most often
          Status VarChar(10) -- queried most often
          Title VarChar(50) -- queried most often
          -- some more columns here
          SomeSmallXML xml -- queried quite often 
          SomeOtherSmallXML xml -- queried quite often 
          MyData varbinary(max) -- queried rarely
          MyOtherData varbinary(max) -- queried rarely
      )
  </code></pre>
  
  <p>IF i move all large value types to other table:</p>
  
  <ul>
  <li>can do online reindexing of clustered index. <strong>But</strong> then i have to move also xml types to
  other table. as they are queried quite often, its does not seem
  reasonable. (i expect fragmentation, because SomeID column is coming
  from client app. Its not reasonable to make other surrogate key as
  clustered index, so SomeID will be key of clustered index.)</li>
  <li>can move large data to slower storage. <strong>But</strong> guess can achieve
  the same by table partitioning (old data in slow filegroup) + indexes
  on fast storage.</li>
  </ul>
  
  <p>In this case do not see very good reasons to move large value data types to other table. 
  i do see reason to set "sp_tableoption N'MyTable', 'large value types out of row', 'ON'".</p>
  
  <p>What is your advice? What else i have to take in account?</p>