Will SQL Server Partitioning increase performance without changing filegroups

Posted by Tom on Stack Overflow See other posts from Stack Overflow or by Tom
Published on 2010-04-23T18:42:54Z Indexed on 2010/04/24 4:13 UTC
Read the original article Hit count: 285

Scenario I have a 10 million row table. I partition it into 10 partitions, which results in 1 million rows per partition but I do not do anything else (like move the partitions to different file groups or spindles)

Will I see a performance increase? Is this in effect like creating 10 smaller tables? If I have queries that perform key lookups or scans, will the performance increase as if they were operating against a much smaller table?

I'm trying to understand how partitioning is different from just having a well indexed table, and where it can be used to improve performance.

Would a better scenario be to move the old data (using partition switching) out of the primary table to a read only archive table?

Is having a table with a 1 million row partition and a 9 million row partition analagous (performance wise) to moving the 9 million rows to another table and leaving only 1 million rows in the original table?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about partitioning