Post reply

When to do table partioning in SQL Server 2005?

  • January 14, 2009 at 9:29 am

    #217435

    Hello all,

    a very basic question: When does it make sense to use table partioning in SQL Server 2005? E.g. which minimum size should a table have so that it makes sense to partition it?

    Thank you!

    JM

  • January 14, 2009 at 9:43 am

    #926700

    I'm watching for better answers, but this is how i understand it:

    suppose you had a table with every address in the united states...200 million or so.

    any search is going to be slow, because of the size of the index.

    if the table is going to be searched a lot by state, you might put the clustered index on STATE and partition the table on state...50 or so partitions, one for each state, which makes searching by state faster, as the index knows which partion the data is in for a matching WHERE statement.

    If it is searched a lot by zip code you might put the Clustered index on zipcode, and then create a bunch of partions with ranges of zipcodes, 00000-11111,11112-22222, etc.

    it's a crappy example, but might get the idea across; how you partition is important on how the data would be searched a lot, so you auto-eliminate ranges of data based on the partion/clustered index.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

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