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 wud 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 - shud 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 recrds in OrgItem*
I would not create a functions to do the work of plain old CASE, unless you need the same functionality in more than one query. Something like: SELECT CASE WHEN Item = 'x' AND Org = 'y' THEN Item.ItemCode ELSE (CASE WHEN OrgItem.OrgItem IS NOT NULL THEN OrgItem.OrgItem ELSE Item.ItemCode END) END FROM OrgItem LEFT JOIN Item ON OrgItem.key = Item.key If you do create a UDF, do not let it do the lookup in the other table. You keep the join in the main query, and you feed the column values to the UDF for evaluation (which will use pretty much the same CASE expression as above). In case you move the lookup action to the UDF, you won't profit from any indexes that do support the join operation in the main query.