Consider a scenario in SQL Server that includes a partitioned table with over 500 partitions. The number of partitioning column values in a temp table are less than 10. We join the temp table to the partitioned table directly on the partitioning column. (Not using $PARTITION, just purely column values.) The query includes joins to other dimension tables in a typical star-schema type way.
Furthermore, there are no non-clustered indexes on the table. The leading column in the clustered PK (ignoring the partitioning key id which is always leading) is a column that joins to one of the star-schema dimension tables and will include a filter. However, the filter on the dimension table would not be selective enough to cause an index seek, if there was an index, if the partitions were just a single table.
So, given the above, why is it that queries on this partitioned table do not always use partition elimination? When would it ever be more efficient to scan a partitioned table than just the partitions required?
At worst a partitioned table with one partition would be the same, ignoring the cost to determine the partition elimination etc.