Stored procedure increases the performance of SQL queries. When we write a query before execution the SQL Server compiler runs three steps. In first step compiler checks the syntax of query then in the second step compiler finds the best way to execute the query and finally in third step query is executed.
So when we write in line queries these three step are always run , but for Stored Procedure the below three step run only once and first two step results are cached for future. So when you execute the stored procedure next time it just executes the last step thus increasing performance.
Below diagram represent the steps of execution plan.
Also see the video of how to optimized SQL query?
Please click here to see more SQL Server interview questions
Pingback: DotNetShoutout