The first one should behave slower, because is bringing a lot more info (it is doing a complete catch of the Table). Instead the second one just brings the firstocurrence and just the info on the Recid field and not the other fields.
The second one is what you normally find in a standard table Exist method.
The Index Hint, for me is significant and you should try to use it every time, but the theory says that if you don´t have the “Allow INDEX hints in queries” in your AOS Server configuration Utility -Tab Database Tuning-, you would have the same performance with o without the Index hint.
I would go for:
return (select firstonly RecId from inventSerial
index hint ItemSerialIdx
where inventSerial.ItemId == itemId &&
inventSerial.InventSerialId == serialId).RecId != 0;
Since there is a cluster index (ItemId, InventSerialld), this will always be picked for this query (when no index hint provided) therefore no Index Hint needed.
Since the cacheLookup property on the table is set then always all fields will be retrieved from the database to be cached, therefore, no difference between having only the RecId field in the query.
FirstOnly operator will add mor CPU computation, since ItemId, InventSerialId is a primary key, this is not needed because it will always return one record.
Since it is always good practice to get only what is needed then I would write the query as:
return (select RecId from inventSerial
where inventSerial.ItemId == itemId &&
inventSerial.InventSerialId == serialId).RecId != 0;
(select firstOnly RecId from inventSerial).RecId
and
(select inventSerial).RecId
are technically the same. The documentation literally says: “The firstOnly qualifier and the field list are implicit in (select…). statements, and are not explicitly needed.”