While talking to some of the attendees at Dev Days last week, I happened to run across between two developers huddled into a corner of the lunch room. I was delighted to join their conversation around  performance tips for writing applications. I was shocked to hear, they had concluded that SQL Server Stored procedures provided no performance advantages over dynamic SQL within applications.

Guys – SQL Server stored procedures improve performance on both Windows and ASP.NET applications. SQL Server automatically looks to optimize performance when a statement is saved as a stored procedure. SQL Server optimizes the commands and stores it in a compiled state, automatically making queries run faster than submitting them as an AD Hoc SQL statement. Also, when using stored procedures, the SQL Server managed provider is able to communicate natively. This automatically, shrinks the round trip time required to query and return the data.

I can safely say that it is ALWAYS good practice to forbid anything other than stored procedures when writing database code.

11:44:51 PM