Skip to Content

In a partitioned table, are indexes "local" by default?

Oct 26, 2016 at 01:20 PM


avatar image

If a nonclustered, nonunique index is added to a partition table, is it automatically made local? Meaning does it index each partition individually or the whole table?

I'm having problems examining the structure of partitioned tables to confirm which indexes are local. Even when I know an index has been explicitly defined as local, the output from <sp_help> is not much different from indexes that were not specified as local.

What is the recommended way to query a table's schema to confirm it is partitioned and has local indexes?


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mark A Parsons Oct 26, 2016 at 02:22 PM

What version of ASE are you running?

Do you have a license for semantic partitioning (or running the developer edition)?

Have you created a semantically partitioned table and index(es)?


Default behavior for create index is to create a global index. For a local index you need to add the local index clause.

sp_help[index] should have a column named 'index_local' to the far right that shows the index as being global or local:

Object has the following indexes

 index_name index_keys index_description ... index_local
 ---------- ---------- ----------------- ... ------------
 idx2        id        nonclustered      ... Local Index
 idx3        name      nonclustered      ... Global Index

sp_helpartition <tablename>,<indexname> should also show the partition type as a global or local index:

1> sp_helpartition t1,idx2
2> go
 name type        partition_type partitions partition_keys
 ---- ----------- -------------- ---------- --------------
 idx2 local index range                   4 id
10 |10000 characters needed characters left characters exceeded