Does SQL Server allow including a computed column in a non-clustered index? If not, why not?
<p>When a column is included in non-clustered index, SQL Server copies the values for that column from the table into the index structure (B+ tree). Included columns don't require table look up. </p>

<p>If the included column is essentially a copy of original data, why does not SQL Server also allow including computed columns in the non-clustered index - applying the computations when it is copying/updating the data from table to index structure? Or am I just not getting the syntax right here?</p>

<p>Assume: </p>

<ul>
<li><code>DateOpened</code> is <code>datetime</code></li>
<li><code>PlanID</code> is <code>varchar(6)</code></li>
</ul>

<p>This works:</p>

<pre><code>create nonclustered index ixn_DateOpened_CustomerAccount
on dbo.CustomerAccount(DateOpened)
include(PlanID)
</code></pre>

<p>This does not work with <code>left(PlanID, 3)</code>:</p>

<pre><code>create nonclustered index ixn_DateOpened_CustomerAccount
on dbo.CustomerAccount(DateOpened)
include(left(PlanID, 3))
</code></pre>

<p>or</p>

<pre><code>create nonclustered index ixn_DateOpened_CustomerAccount
on dbo.CustomerAccount(DateOpened)
include(left(PlanID, 3) as PlanType)
</code></pre>

<p>My use case is somewhat like below query. </p>

<pre><code>select 
    case 
        when left(PlanID, 3) = '100' then 'Basic'
        else 'Professional'
    end as 'PlanType'
from 
    CustomerAccount
where
    DateOpened between '2016-01-01 00:00:00.000' and '2017-01-01 00:00:00.000'
</code></pre>

<p>The query cares only for the left 3 of <code>PlanID</code> and I was wondering instead of computing it every time the query runs, I would include <code>left(PlanID, 3)</code> in the non-clustered index so the computations are done when the index is built/updated (fewer times) instead at the query time (frequently)</p>

<p>EDIT: We use SQL Server 2014.</p>