title: Trying to improve performance on a badly designed table
body: <p>I have a table, which unfortunately, got designed in an interesting way. It has 30 columns which are all <code>NVARCHAR(MAX)</code>. It was designed to be a quick 'data warehouse style' table, but it's transactional.</p>
  
  <p>One column is a <code>bit</code>, called <code>IsPhysical</code>. The table has a PK, and that's all as far as indexing. All those <code>NVARCHAR</code> columns are used within <code>WHERE</code> clauses. The table isn't massive. Between 100K rows and 500k. </p>
  
  <p>But because of the type of queries we do, which are often,</p>
  
  <pre><code>WHERE 
      ISNULL(a.ColumnA, '') = ISNULL(b.ColumnA, '') 
      AND ISNULL(a.ColumnB, '') = ISNULL(b.ColumnB, '')` 
  </code></pre>
  
  <p>where <code>ColumnA</code>, <code>ColumnB</code> etc are of type <code>NVARCHAR(MAX)</code>.</p>
  
  <p>We're suffering from some performance issues. </p>
  
  <p>Two columns we often have in our <code>WHERE</code> clause are <code>IsActive</code> and <code>IsPhysical</code>. So, I was going to apply an index on those, to see if we can get any benefit. </p>
  
  <p>So prior to that, I did a query:</p>
  
  <pre><code>SELECT * 
  FROM MyTable 
  WHERE IsActive = 1
  </code></pre>
  
  <p>What I found strange was: why does the execution plan report a 'Clustered index scan' on that query? The table has a clustered index on the Id column - which is a <code>UNIQUEIDENTIFIER</code>. I'm not sure what the benefit of a clustered index on a <code>UNIQUEIDENTIFIER</code> is, but that's something else to investigate.</p>
  
  <p>99% of activity on the table are reads. So, I was hoping to apply indexes to some of those columns, but because they're <code>NVARCHAR(MAX)</code> - that option is not available. And changing them to <code>VARCHAR(800)</code> would break the 8000 byte row limitation. </p>
  
  <p>So my only option to TRY help things, is to index those <code>IsPhysical</code> and <code>IsActive</code> columns, and maybe change the index type on the PK? But not sure why, before adding indexes to those columns, I'm getting a 'Clustered index scan'.</p>