Chapter 10: Partitioning Tables and Indexes

Local nonclustered indexes

You can define local nonclustered indexes on any set of indexable columns.

Using the publishers table partitioned by range on the pub_id column as in “Global nonclustered index on partitioned table”, create a partitioned, nonclustered index on the pub_id and city columns:

create nonclustered index publish8_idx (A)
	on publishers(pub_id, city)
	local index p1, p2, p3

You can also create a partitioned, nonclustered index on the city column:

create nonclustered index publish9_idx (B)
	on publishers(city)
	local index p1, p2, p3

Figure 10-8 shows both examples of nonclustered local indexes. The graphic description of each is identical. However, you can enforce uniqueness on example A; you cannot enforce uniqueness on example B. See “Guaranteeing a unique index”.

Figure 10-8: Local nonclustered indexes

This figure shows twp versions of a local nonclustered index. Example A is indexed on pub_id and city. The index partition key is on pub_id. Example B is indexed on city. The index partition key is on pub_id. In both cases, the table is partitioned on pub_id. Only Example A can guarantee uniqueness.

View this book as PDF