chapman.tim (10/1/2010)
Solomon is correct. Partitioning is NOT intended to increase query performance, it is intended to increase data manageability and administration. SQL 2008 did some things to make better parallel plans against partitioned tables, but it still doesn't really do much in terms of helping query performance. Often, if misused, it can hinder performance significantly.
Agreed, and if your database is of such a size that you can not reasonably do a full backup, then filegroups and data partitioning is the way to go.
However, I think it will also give you performance gains as a bonus. If you partition your data sensibly, then SQL will not have to read through millions and millions of rows to satisfy the criteria, just through the partitions that are required. That's got to give a performance gain.