Will SQL Server Partitioning increase performance without changing filegroups

Question

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?

Answer 1

Well first partitioning will only help you if you are using the enterprise version. I believe it will improve your performance, although the actual benefit you'll get will depend on your specific work load (yhea, it always depends).
It is not exactly as creating 10 smaller tables but if your queries run against the ranges of your partitions the data in that partition will only be "touch". In those cases I think the performance improvement will be noticeable. In cases where queries run across the partition ranges the performance will be worse.
I think you'll have to try what solution fits your data the best, in some cases partition will help you and in some other will do the opposite, another benefit from partitioning is that you won't have to worry about to move your data around.

This is a good article about partitioning - bol

Answer 2

Partitioning is not a performance feature, is for maintenance operations like moving data between tables and dropping data real fast. Partitioning a 10M rows table into 10 partitions of 1M rows each not only that it won't increase most queries performance, will likely make quite a few of them slower.

No query can operate against a smaller set of rows of a single partition unless the query can be determined that it only needs row in that partition alone. But this can be solved, always, and much better, by properly choosing the clustered index on the table, or at least a good covering non-clustered index.