Deploy view or use function for lookup

Question

I've two master tables Org & Item and then there's an OrgItem table.I have to fetch ItemCodes for a particular Org.

TABLE STRUCTURE:

• Org ( Id, OrgCode, Name) - Org Master table
• Item ( Id, ItemCode, Name) - Item Master table
• OrgItem ( ItemId, OrgId, ItemCode) - Some Org specific ItemCodes

---

Now only some Item's have Org specific ItemCode so by default I fetch the ItemCode from Item but if for a particular Item+Org combination if its record exists in the OrgItem table then I have to fetch the ItemCode from the OrgItem table.

Creating a View_OrgItem_Item might be a solution but it would mean a view which consists of n-fold the size of the original Item table (where n = number of Orgs).

Another option is to create a GetItemCode(ItemId, OrgId) which does the lookup and return either Item.ItemCode or ORgITem.ItemCode

So, my question is - should I go for a 'View_OrgItem_Item' or simply create a function GetItemCode(ItemId, OrgId)?

Which one is best in terms of performance? Any other factors to consider.

*Note: It is NOT necessary that ALL the Orgs have records in OrgItem

Answer 1

It's almost always going to be the case that the view will perform better than using a function. This is because you will often want to get the item code in a set-based fashion. With a function, this lookup has to happen under the covers, row by row. With a view, you can do a simple join and the query optimizer can choose more optimal plans than a row-by-row lookup, which is what the function essentially forces.