Post reply

Table Partitioning

  • July 20, 2012 at 5:32 am

    #264119

    Please help me with this cumbersome partitioning scenario

    Need to partition a table based on following status value of a column - A,B,C,D

    I want three partitions:

    Partition1 --> Status Value (A,C)

    Partition2 --> Status Value (B)

    Partition3 --> Status Value (D)

    How to do this?

    I don't want to create a separate Partition for status value A and C

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 20, 2012 at 5:42 am

    #1515456

    One of the articles that helped me when I was faced with a similar issue

    http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 20, 2012 at 6:13 pm

    #1515810

    thanks for your link. I have already seen this before. the real challenge here is to define range function. As mentioned above if I assign a status range from A to C to Partition 1, then how could I define Partition 2 with status B?

    I am curious that some one should respond with a way to do it.

    Thanks

    Lokesh

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 21, 2012 at 12:55 am

    #1515828

    Could some please help me with this. I need to implement this in a project.

    Thanks, Lokesh

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 21, 2012 at 2:46 pm

    #1515886

    lokeshvij (7/20/2012)

    the real challenge here is to define range function. As mentioned above if I assign a status range from A to C to Partition 1, then how could I define Partition 2 with status B?

    You cannot do it in SQL Server, not directly. SQL Server only supports RANGE partitioning, LIST partitioning is not supported (yet).

    If you really have to do it, you have to add a new column for a surrogate partition key which you will populate this way:

    surrogate-partitioning-key = 1 when status = A or C.

    surrogate-partitioning-key = 2 when status = B

    surrogate-partitioning-key = 3 when status = D

    Once you got there, range partition your table by surrogate-partitioning-key.

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • July 21, 2012 at 8:49 pm

    #1515919

    Thanks Paul. Wonderful trick for my implementation.

    Lokesh

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply