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.
Comments
Note that developers shouldn't "commonly" write code like this. They should follow the static exist method design pattern.
Category: Development
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
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