The other day out at Microsoft I ran into some interesting behavior in SQL Server regarding the difference between running a query in a table-valued function versus running it in a stored procedure.

This particular query I was trying to run selects columns from an existing view that is part a database of the Microsoft product that I am currently working with. Unfortunately, this is no ordinary view. This view not only has two nested select statements, it joins on a table holding thousands and thousands of records. Looking at the estimated execution plan was ridiculous, having probably 50 or 60 processes.

Running this query in a table-valued function, basically just running the view, causes the CPU on the server to soar to 100% and Management Studio to basically timeout. However, placing the same query in a stored procedure yields almost instant results.

I talked this over with the resident SQL guru on the project and all we could come up with was that, because of the complexity of execution plan, the pre-compilation of the query in a stored procedure makes all the difference.