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'
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