Partition table & index

Hi Friends,

Please as I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . help me how I can create partition index on tablea that first time create partition  index  and next time I want to rebuild index only on y2015 partition . 

thank you.

regards,

<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></
July 26th, 2015 11:52am

Hi ,

You can always build the index based on partition number. in your case it is 4.

example :

-- with partition number 
alter index idx_indexname on tablename rebuild  partition = 4;
-- build all
alter index idx_indexname on tablename rebuild  partition = ALL;
-- OR Simply
alter index idx_indexname on tablename rebuild

Here is msdn link for alter index => ALTER
July 26th, 2015 1:06pm

thank you for reply, but how I can build partition index for each partition the following will create partition index for all partition  and later on I can rebuild them seperatly

CREATE CLUSTERED INDEX partindex ON T1(A) ON PS(A)

In addition to my question we can also seperatly update statisics for each partition .

thank you.

regards,

July 26th, 2015 1:20pm

Hi 

You cannot build a clustered index for specific partition, In sql server there is no way we can build the index for each partition and merge them afterward.

I am sure what use case you want to satisfy here, but you can always build the filtered non-clustered index for that partition(by specifying the where clause for that partition only

Yes the update for statistics for each partition using syntax mention below, but this work only for stats which build with incremental condition on the statistics

UPDATE STATISTICS table_or_indexname with resample on partition 1;

here you can study about incremental statistics: http://sqlperformance.com/2014/02/sql-statistics/2014-incremental-statistics

Free Windows Admin Tool Kit Click here and download it now
July 26th, 2015 1:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics