6
Developers quite commonly use "select firstonly recid from myTable where field = x" only to check whether a record does exist. When the recid is not the primary key of this table, SQL will build a query plan including a key lookup.
Example: "Select firstonly recid from custtable where accountnum == 123" will fist seek the index on accountnum, then do a lookup in the clustered index, to retrieve the recid.
My proposed solution would be to introduce a keyword for 'PrimaryKey' which will be replaced by the primary key field before contacting SQL. Example "Select firstonly PrimaryKey from custtable where accountnum == 123". The query processor should replace the keyword by the primary key (AccountNum) before contacting SQL.
Category: Development
STATUS DETAILS
Needs Votes

Comments

G

Note that developers shouldn't "commonly" write code like this. They should follow the static exist method design pattern.

Category: Development

G

The problem mentioned by Denis Trunin is indeed what happens when a developer looks up the primary key and writes this query manually. In my opinion, it is also exactly how this keyword should not be interpreted in my opinion. The way I see it, part of the point here is there should be a way to select the primary key of a table WITHOUT selecting the RecId (too).

Category: Development

G

Any way if you wirte
Select firstonly AccountNum from custtable where accountnum == 123
resulting SQL command will be
Select top 1 AccountNum, RecId from custtable where accountnum = 123
 
so your solution doesn't remove the RecId from select

Category: Development