Home Dashboard Directory Help

Introduce Substitute Primary Keys in the Optimization Phase by lasa



Sign in
to vote
Type: Suggestion
ID: 800107
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.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Substitute Primary Key.sql 9/10/2013 2 KB