What is key_ordinal = 0 for index columns
<p>In MSSQL, I have index on a partitioned table, for which I dont understand the meaning of the property <code>key_ordinal</code> when it's equal to zero.</p>

<p>To create the index, the partition scheme is based on CUT_OFF.</p>

<pre><code>create table dbo.TESTTB (CUT_OFF datetime, number int) on partscheme(CUT_OFF);
create index TESTIX on dbo.TESTTB (number) on partscheme(CUT_OFF);
</code></pre>

<p>The query shows the values of <strong>key_ordinal</strong>:</p>

<pre><code>select c.name as column_name, b.key_ordinal
from sys.indexes a
inner join sys.index_columns b 
    on  a.object_id = b.object_id and  a.index_id = b.index_id
inner join sys.all_columns c on b.object_id = c.object_id
    and b.column_id = c.column_id 
where a.name = 'TESTIX'
</code></pre>

<p>The results are:</p>

<pre><code>column_name key_ordinal
CUT_OFF     0
number      1
</code></pre>

<p>The column <strong>CUT_OFF</strong>:</p>

<ul>
<li>Is showed in <code>sys.index_columns</code> with <strong>key_ordinal = 0</strong></li>
<li>Is missing in <code>Create Index</code> statement</li>
<li>Is missing in index information from <code>sp_help 'TESTTB'</code></li>
</ul>

<p><a href="https://msdn.microsoft.com/en-us/library/ms175105.aspx" rel="nofollow">MSDN says:</a> </p>

<blockquote>
  <p>0 = Not a key column, or is an XML index, a columnstore index, or a
  spatial index.</p>
</blockquote>

<p>What does it means?
What is <strong>key_ordinal = 0</strong>?</p>

<p>Thanks</p>