title: How to improve performance in SQL Server table with image fields?
body: <p>I'm having a very particular performance problem at work!</p>
  
  <p>In the system we're using there's a table that holds information about the current workflow process. One of the fields holds a spreadsheet that contains metadata about the process (don't ask me why!! and NO I CAN'T CHANGE IT!!)</p>
  
  <p>The problem is that this spreadsheet is stored in an IMAGE field in an SQL Server 2005 (within a database set with SQL 2000 compatibility).</p>
  
  <p>This table currently has 22K+ lines and even a simple query like this:</p>
  
  <pre><code>SELECT TOP 100 *
    FROM OFFENDING_TABLE
  </code></pre>
  
  <p>Takes 30 seconds to retrieve the data in Query Analyser.</p>
  
  <p>I'm thinking about updating the compatibility to SQL 2005 (once that I was informed that the app can handle it).</p>
  
  <p>The second thing I'm thinking is to change the data-type of the column to <code>varbinary(max)</code> but I don't know if doing this will affect the application.</p>
  
  <p>Another thing that I'm considering is to use <code>sp_tableoption</code> to set the <code>large value types out of row</code> to <code>1</code> as it's currently <code>0</code>, but I have no information if doing this will improve performance.</p>
  
  <p>Does anyone know how to improve performance in such scenario?</p>
  
  <hr>
  
  <p><strong>Edited to clarify</strong></p>
  
  <p>My problem is that I have no control on what the application asks to the SQL Server, and I did some Reflection on it (the app is a .NET 1.1 website) and it uses the offending field for some internal stuff that I have no idea what it is.</p>
  
  <p>I need to improve the overall performance of this table.</p>