locked
Partition table & index RRS feed

  • Question

  • 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;"></iframe>

    asad

    Sunday, July 26, 2015 3:51 PM

Answers

  • 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


    Regards Harsh

    • Marked as answer by malikasad Sunday, July 26, 2015 8:56 PM
    Sunday, July 26, 2015 5:47 PM

All replies

  • 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 INDEX

    Regards Harsh


    Sunday, July 26, 2015 5:05 PM
  • 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,


    asad

    Sunday, July 26, 2015 5:19 PM
  • 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


    Regards Harsh

    • Marked as answer by malikasad Sunday, July 26, 2015 8:56 PM
    Sunday, July 26, 2015 5:47 PM