Home Dashboard Directory Help
Search

Introduce Substitute Primary Keys in the Optimization Phase by lasa


Status: 

Active


8
0
Sign in
to vote
Type: Suggestion
ID: 800107
Opened: 9/10/2013 3:28:19 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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.
Details
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