Introduce Substitute Primary Keys in the Optimization Phase - by lasa

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 800107 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 9/10/2013 3:28:19 AM
Access Restriction Public


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.

See the attached script for an example where table elimination is not achieved, but could be in theory, and if done in practice would increase performance.
Sign in to post a comment.
Posted by Microsoft on 10/3/2013 at 5:38 PM
Thank you for submitting this feedback. We are investigating the issue and will update you when we have more information.