When I first found out about functions in SQL Server, I thought it would be a silver bullet to modularize my code.
In the end, I realized performance suffers greatly, and functions need to be used sparingly, and sql needs to be written in a way to get around the performance hit.
For example, I could set up a segment of customers (“Cool People”) in a database and have all my procedures to refer to my function to pull the “cool” customers. The actual definition and/or list of cool customers may change, but I only need to change it in one place and it will disseminate through all my code.
BUT, if you are dealing with complex queries or larger data sets, then you’ll be surprised how significantly it will slow down your procedures.
If i nest “where customer_id in (select * from udf_coolPeople)” and that is part of a complex query.. watch out and wait..
One strategy is to create a temp table that lists the cool people, so this approach should perform just fine for both scalar and table based functions:
CREATE TABLE #T_COOL (customer_id varchar(30))
Insert into #T_COOLÂ SELECT * FROM udf_coolPeople
select * from tblProducts where customer_id in (select customer_id from #T_COOL)
I don’t know all the workings of the engine, I just drive the car a LOT and have found best practices through trial and error. But the main problem is that SQL has a very intelligent approach and builds a query engine plan, and the function is like a black box and doesn’t offer any visibility.
