indexes and partitions:Creating an index on a specific partition

Question

 

Hi Evryone

 

I have just partitioned several tables and now i would like drop and create indexes per partition.So far i discovered that I can rebuild an existing index on a specific partition using :

 

ALTER INDEX IX_COL1

ON dbo.PartTable

REBUILD Partition = 4;

go 

Now I would like to know the code for dropping an index on a specific partition as well as the code for creating an index on a specific partition.

 

best regards

Globalwarming

---

newbie

Answer 1

You can align the indexes to the table or you can partition them independently (unaligned), but you can not have independent indexes per partition.

Special Guidelines for Partitioned Indexes

AMB

Answer 2

you can create or drop partition index see the below code.

CREATE NONCLUSTERED INDEX IX_orders_aligned
50.   
ON dbo.orders(order_id)
51.   
ON test_monthlyDateRange_ps(orderDate);

drop index index_name

 

Answer 3

 

Hi Evryone

 

I have just partitioned several tables and now i would like drop and create indexes per partition.So far i discovered that I can rebuild an existing index on a specific partition using :

 

ALTER INDEX IX_COL1



ON dbo.PartTable



REBUILD Partition = 4;



go 

Now I would like to know the code for dropping an index on a specific partition as well as the code for creating an index on a specific partition.

 

best regards

Globalwarming

---

newbie

You can't drop the index from the specific partition , But if u merge the partition the index will automatically be moved from the exisiting partition to the partition which you specify as target.

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 4

Mohammad

 

Why does sql allow me to rebuild in index on a specific partition?

---

newbie

Answer 5

Rebuilding per partition allows you to remove fragmentation from just a subset of the index, or to move the subsection to a new filegroup, but the index belongs to the whole table. You cannot have an index on only part of a table (i.e. one partition).

---

HTH, Kalen Delaney www.SQLServerInternals.com