title: T-SQL - Alter index, add INCLUDE columns
body: <p>I have a table</p>
  
  <pre><code>CREATE TABLE Test
  (
      id int primary key,
      a nvarchar(max),
      c nvarchar(max),
      b nvarchar(max)
  )
  </code></pre>
  
  <p>I want to improve performance of queries to the table and want to create index (or indexes) for columns that are accessed often.</p>
  
  <p>As all fields except <strong>id</strong> has type <strong>nvarchar(max)</strong> so sql-server says that I can't create index for columns where type is nvarchar(max)</p>
  
  <pre><code>Create index MyIndex on T(a)
  </code></pre>
  
  <blockquote>
    <p>ERROR: Column 'a' in table 'T' is of a type that is invalid for use as a key column in an index</p>
  </blockquote>
  
  <p>According to the <a href="http://msdn.microsoft.com/en-us/library/ms188783.aspx" rel="nofollow" title="MSDN">MSND</a> it is possible to create index using followed notation <br></p>
  
  <pre><code>CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
      ON &lt;object&gt; ( column [ ASC | DESC ] [ ,...n ] ) 
      [ INCLUDE ( column_name [ ,...n ] ) ]
  [ ; ]
  </code></pre>
  
  <p>I'm interested in the option <pre>[ INCLUDE ( column_name [ ,...n ] ) ]</pre>
  About it MSDN says:</p>
  
  <blockquote>
    <p>INCLUDE (column [ ,... n ] ) Specifies the non-key columns to be added
    to the leaf level of the nonclustered index. The nonclustered index
    can be unique or non-unique. All data types are allowed except <strong>text</strong>, <strong>ntext</strong>, and <strong>image</strong>.</p>
  </blockquote>
  
  <p>Can I create any index or alter existing PK index in order to add columns to the index?</p>