Conclusion

User-defined functions using the T-SQL language are a great way to extend the functionality of SQL Server 2005 when you want tight integration with queries, such as supplying conditions on a WHERE clause or creating a calculated column. T-SQL UDFs possess a major limitation: they must be deterministic and, as such, cannot alter the database.

To alter the database, you must use a T-SQL stored procedure. Its a good idea to use T-SQL stored procedures to update the database when your business logic needs tight integration with database operations. Using T-SQL stored procedures for updates has the added advantage of being parameterized by definition, thus making them less prone to a security issue known as SQL Injection.

Neither T-SQL UDFs nor stored procedures are the best alternative if your code contains little database manipulation and a great deal of unrelated logic, such as string handling and numeric calculation. In those situations, it is best to use your standard high-level language and run the code in a machine other than the database server such as the client, a Web server, or a component server, depending on your application architecture. If you want both tight database integration and logic-intensive operations, you may find that T-SQL is not well suited to the task due to poor performance or lack of some advanced programming features. In those cases, you can use a .NET language, such as Visual Basic.NET, C#, or C++, to write the logic- intensive code and call it from inside SQL Server 2005 itself using SQL Server CLR functions and procedures.