title: SQL Server - index on a computed column?
body: <p>I'm joining to a table dozens of different times, and every time, I join (or filter) based on the results of a SUBSTRING of one of the columns (it's a string, but left-padded with zeros, and I don't care about the last four digits). As a result, even though this column is indexed and my query would use the index, it does a table scan because the SUBSTRING itself isn't indexed, so SQL Server has to calculate it for every row before as it's joining.</p>
  
  <p>I'm looking for any ideas on how to speed up this process. Currently, there's a view on the table (It's a "SELECT * FROM", just to give the table a friendly name), and I'm considering adding a column to the view that's computed, and then indexing that. I'm open to other suggestions, though - any thoughts?</p>
  
  <p><strong>MORE DETAIL:</strong>
  I should have shared this to begin with. The table receives replication from our billing system, so editing the underlying table to add a computed column is not an option. Any computed column would have to be added to the view on the table. Also, the leading zeros aren't always leading zeros - they're sometimes other data that I'm not interested in. I suppose the real question is "<strong>How can I join to data in the middle of a VARCHAR column while also making use of an index? Full-text Search?</strong>"</p>
  
  <p><strong>Clarifying my example</strong>
  I'm simplifying, but essentially, let's say I'm trying to look up values in a column with the following values:</p>
  
  <pre><code>00000012345MoreStuff
  00000012345Whatever
  19834212345
  Houses12345837443GGD
  00000023456MoreStuff
  </code></pre>
  
  <p>I'm interested in rows where SUBSTRING(7,5)="12345", so I'd want rows 1-4, but not row 5. What I'm proposing is adding a column to my "SELECT *" view that has this substring in it, and then indexing based on that. Does that make more sense?</p>