• sf.onlineforums (9/19/2013)


    Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?

    That's indeed a valid concern. If your queries typically go by supplier disregarding the data, you will need to have indexes on supplier which is not partitioned-aligned. since else the data for the supplier is spread out all over the partitions, and performance will suffer.

    Then again, if you are not loading or dropping data by supplier, I am not sure that there is much point of partitioning by supplier at all.

    if you have a limited number of supplieries, one option would be to construct a an artifical key which is a combination of supplier and date, and now you can have the sub-partitions you desired. But you will need to modify you queries to use a range on this artificial key to query for a certain supplier.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]