none
Possible to create Index on partition table for specific partition only in sql server 2012

    Question

  • Hi All,

    Is it possible to create Index on partition table for specific partition only in sql server 2012? Please anyone can provide the syntax?

    I know we can rebuild an index for specific partition.

    ALTER INDEX IX_TransactionHistory_TransactionDate ON Production.TransactionHistory REBUILD Partition = 5;

    BUt not aware that we can create it or not only to specific partition. Please respond.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, August 23, 2013 8:11 AM

Answers

All replies

  • "The question does not compute"...

    What should the result look like?

    A table with 10 Partition, for example by year? But then, is it just a heap? - hopefully it's a clustered indexed table. Then what is the index partitioned by? Those 10 of course.

    So then You want a second Nonclustered Index with the same data but all on the same partition?

    The index has its own partitions. But of course it can be partitioned differently.

    You may wanna read more about this topic: http://technet.microsoft.com/en-us/library/ms190787.aspx

    So far the answer is "No" - or "what exactly do you want to accomplish? - please rephrase"


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 24, 2013 7:11 PM
  • No, indexes are per table rather than per partition.  The execution plan for a partitioned table assumes the same index can be used regardless of partition.  However, each partition of a partitioned index can have different index properties, such as row compression, page compression or none because that doesn't affect the plan.

    Another method to accomplish this is with a partitioned view rather than a partitioned table.  In that case, each underlying table can have different indexes because each table can be accessed with different physical operators and indexes.

    Can you elaborate on why you would like to have different indexes for each partition?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 24, 2013 7:59 PM
    Moderator
  • Maybe now I got what you want:

    You could create a filtered index (explained in detail here: http://technet.microsoft.com/de-de/library/cc280372.aspx) which only stores data which matches a certain criteria - which could be like a certain year only

    CREATE NONCLUSTERED INDEX NCLFI_Date_2013
        ON schema.table (Date , col2)
        WHERE Date >= '20130101' AND Date <= '20131231' ;

    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Sunday, August 25, 2013 10:00 AM