samedi 18 juin 2016

What is key_ordinal = 0 for index columns

In MSSQL, I have index on a partitioned table, for which I dont understand the meaning of the property key_ordinal when it's equal to zero.

To create the index, the partition scheme is based on CUT_OFF.

create table dbo.TESTTB (CUT_OFF datetime, number int) on partscheme(CUT_OFF);
create index TESTIX on dbo.TESTTB (number) on partscheme(CUT_OFF);

The query shows the values of key_ordinal:

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'

The results are:

column_name key_ordinal
CUT_OFF     0
number      1

The column CUT_OFF:

  • Is showed in sys.index_columns with key_ordinal = 0
  • Is missing in Create Index statement
  • Is missing in index information from sp_help 'TESTTB'

MSDN says:

0 = Not a key column, or is an XML index, a columnstore index, or a spatial index.

What does it means? What is key_ordinal = 0?

Thanks

Aucun commentaire:

Enregistrer un commentaire