locked
Table Partitioining RRS feed

  • Question

  • Hi everyone,

    I'm currently working on a case study on  partitioning.

    http://social.technet.microsoft.com/wiki/contents/articles/22630.database-partitioning.aspx

    I decided to partition the table using 'partitioning key'.

    I want to partition the table using exact match on the partitioning key rather than

    range values. I want to implement some sort of physical data grouping readable to human user

    that works exactly with the underlying physical data partitioning.

    If there are sugesstion on how to implement this. please feel free to comment on this link

    thanks,

    joey


    http://joeydj.com/

    Tuesday, January 21, 2014 12:17 PM

Answers

All replies

  • Tuesday, January 21, 2014 12:20 PM
  • very nice discussion points


    http://joeydj.com/

    Tuesday, January 21, 2014 12:36 PM
  • >As a member of  Crosswind business intelligence team, you are assigned to partition the
    >customer table using the following fields: Region, Country, 'isactive'


    So you push back on this, explaining that SQL Server only supports a single partition column.  And so you can either choose one of those columns to partition on, and use the others in the clustered index, or you can concatenate the three columns together and partition on that. 

    When you partition on a computed column like this, your partition scheme is very, very unlikely to be useful in queries. For instance a query like "select * from Customer where Country = 'USA'" will have to scan all partitions.

    Most of the time this is not a good idea, and you should try to understand the queries better to identify the best physical design, which probably doesn't involve partitioning on a computed column.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by JoeyDj Wednesday, January 22, 2014 12:13 AM
    Tuesday, January 21, 2014 4:17 PM
  • Hi David,

    You are correct. Also I cannot make use of the primary key which is unalligned.

    This will prevent me from doing partition switch as well.

    I'm trying to make use of the partition for me to be able to quickly locate

    my data even without running a query. I'm designing the partition in such a

    way that the partition key is a 'data group' which is tells the user what sort of data

    is this. It is some kind of a metadata within the data.

    I think the work around is put the metadata on a separate table and implement

    a value key for each country like the country code.

    I have to revise the case study.

    thanks,

    joey


    http://joeydj.com/


    • Edited by JoeyDj Wednesday, January 22, 2014 12:13 AM
    Tuesday, January 21, 2014 11:47 PM
  • Hi everyone,

    I added this rational for this requirements

    The rationale behind the 3 composite fields partitioning are as follows:

    1. Regional data should stay together for regional replication.
    2. Country data should stay at least adjacent to each other for country's
        delivery center replication.
    3. non-active data will contain historical changes and will be barely use by the system
    .
    4. Partition metadata will be used for metrics purposes
    .
    5.  Insert, update and delete shall be contained on the country's 2 partition


    any suggestion or ideas will be appreciated. thank a lot for the comments


    http://joeydj.com/

    Wednesday, January 22, 2014 5:39 AM