title: partitioning big tables  - indexes
body: <p>i ve got assigned a task to improve data management proces(data archivization) on couples of tables
  tables are like 200gb</p>
  
  <p>i am reading now about table partitioning and best practices 
  and as far as i know now the process goes like </p>
  
  <ul>
  <li>create filegroups and files</li>
  <li>create partitioning function </li>
  <li>partitioning scheme - (map intervals to appopriate filegroups)</li>
  <li>recreate clustered index - this is the moment when table  is physically moved to another files </li>
  <li>profit :) </li>
  </ul>
  
  <p>but cant find one information 
  what is going on with existing non clustered indexes at this point ?
  from here : <a href="http://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx</a>
  i have found</p>
  
  <blockquote>
    <p>Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table. As a result, the index is partitioned in essentially the same manner as the table. This makes the index aligned with the table. </p>
  </blockquote>
  
  <p>and another one </p>
  
  <blockquote>
    <p>When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonunique, nonclustered index, SQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. SQL Server does not add the partitioning column to the index if it is already present in the index.</p>
  </blockquote>
  
  <p>but none of this reference my problem 
  do i have to create partitioning function explicitly for existing non clustered indexes which does/doesnt have partitioning column in their definition ?</p>
  
  <p>lets say we have table like </p>
  
  <p>table A - 
  col1 col2 col3</p>
  
  <p>with clustered index on col1 
  and non clustered on col 3 
  on PRIMARY partition</p>
  
  <p>what is going to happen with non clustered index on col3 after partitioning , is it going to be aligned with table or still resides on PRIMARY partition </p>