While working with SQL Server, we have identified a few shortcomings that we have reported to Microsoft. To raise awareness for these feature request we would appreciate your help in voting for their importance. In order to do so, follow the links below, log in with your Microsoft ID and click on the green box with an up arrow.
When parts of a primary key is used in a calculation, the result of that calculation may be used instead of a column that is part of the primary key, and still fulfill all the requirements of being a primary key. For example, if the primary key in a table is (id, stamp) and a view is created as select id, stamp, row_number() over (partition by id order by stamp desc) as ver… then (id, ver) can be used as a substitute primary key for (id, stamp). The query optimizer could use such information in order to do table (join) elimination in the case that the view is joined on id, ver instead of id, stamp.
When the execution plan contains multiple paths with identical sections, these could be consolidated and only executed once, instead of once per path. See the example below where three paths could be consolidated, with a substantial gain in performance.
In certain circumstances, when a primary key is defined in the table returned from a Multistatement Table-valued Function, the table does not have to be materialized at all, since it does not take part in the execution. This is similar to the already existing table elimination done between tables that have primary keys (and foreign keys) defined. Please see the attached script for a scenario in which the table is “unnecessarily” materialized.
Also, if you can figure out workarounds, let us know. Thanks!