Will SQL Server Partitioning increase performance without changing filegroups
- by Tom
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?