SQL Server

SQL Server: Interesting Function and Stored Procedure Behavior

Standard

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.

The First Project

Standard

Well, the training was finally over and the start of March brought the start of my first project as a consultant at Avanade.

After a little bit of networking with a fellow WWU grad, I got onto my first project, and wouldn’t you know it, it’s at Microsoft. I am currently working in a small team as a UI developer working with ASP.NET, C#, and SSRS, which is quite an exciting opportunity considering ASP.NET and SQL Server are my bread and butter. There was only one problem.. I (and my trusty new laptop) had to be converted to the Microsoft religion before being able to do pretty much anything. So after a new laptop image, hours in software installs and reinstalls, badge creation, badge recreation, and a smart card reader I was finally fully capable of working at Microsoft.

As developing on this project starts I have decided that I will be expanding the scope of my weblog to not only include life snippets but also some code snippets and developer thoughts that I find useful or interesting along the way.