Post reply

Partitioning Question

  • January 28, 2008 at 12:20 pm

    #119797

    is their a way to partition a table using a column from a table that we have a foreign key relationship with. We would want to partition both tables using the store_no values that can only be found on the orders table.

    Orders (orderid int, store_no int, data etc)

    OrderDetail (orderid int, data etc)

    Is there a way to do this for the OrderDetail since it does not have this column? I was originally thinking os some sort of computed column or do we need to add the Store_no column to the orders detail

    🙂

  • January 28, 2008 at 7:51 pm

    #772711

    Short answer - no.

    Quick question - why are you partitioning?

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • January 29, 2008 at 6:27 am

    #772817

    the tables are getting very large, over 500 million rows in orderdetails. Would like to archive, but client does not want it at this time. Figurred partitioning would hopefully rreduce the amount of data that has to be looked. The db has been fine tuned, indexes have been built and are being maintained for statistics. Response time overall is good, but we we encounter heavy activity it tend to slow a bit.

    I am trying to avoid the alter to add additional columns on the orderdetails since it is so large in the first place and the site is 24 X 7.

  • January 29, 2008 at 6:40 am

    #772826

    I would move to Kimberly's favorite architecture of having a single table with current, read/write data - plus a partitioned table with older read-only data, with partitioned views over the two tables. That way you can different indexes and columns in the two tables and the views over everything hide the differences when necessary.

    See her blog post at http://www.sqlskills.com/blogs/kimberly/2007/10/03/SQLServer2008OffersPartitionlevelLockEscalationExcellentBut.aspx

    Hope this helps a bit.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • January 29, 2008 at 10:20 am

    #772973

    Paul,

    Thank you for responding, Your comments are very much appreciated

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

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