What is a Stored Procedure?

A stored procedure is one or more Transact-SQL statements that are compiled and stored within SQL Server for very fast execution. If you are familiar with programming languages, you can think of a stored procedure as a function. The two are very similar, except that stored procedures are called from within another function, subroutine, or process. A stored procedure is said to encapsulate functionality because you do not necessarily need to know how the stored procedure is coded. All you need to know is 1) what the stored procedure does and 2) the arguments that it expects.

Stored procedures can execute faster than other Transact-SQL statements in SQL Server 2019. There are a few reasons for this:

• Pre-compiled: Stored procedures are pre-compiled, which means that SQL Server has already analyzed the code and created an execution plan. This means that the procedure can be executed much faster than a Transact-SQL statement that has to be compiled on the fly.
• Parameterization: Stored procedures can use parameters, which can help to improve performance by avoiding the need to recompile the procedure for each different set of data.
• Reusability: Stored procedures can be reused multiple times, which can help to improve performance by avoiding the need to write the same code multiple times.
• Reduced network traffic: If a stored procedure is called from a client application, only the name of the procedure and the parameters need to be sent to the server. This can help to reduce network traffic and improve performance.

However, there are also some situations where stored procedures may not be the best choice. For example, if you are only going to execute a query once, it may be more efficient to simply write the query directly in your application code. Here are some of the benefits of using stored procedures:

• Improved performance: Stored procedures can be pre-compiled and cached, which can improve performance.
• Increased security: Stored procedures can be used to enforce security by restricting access to certain data and operations.
• Reduced development time: Stored procedures can be used to modularize your code, which can make it easier to develop and maintain.
• Improved code clarity: Stored procedures can be used to document your code, which can make it easier to understand and maintain.

Here are some of the drawbacks of using stored procedures:

• Increased complexity: Stored procedures can add complexity to your application.
• Reduced flexibility: Stored procedures are less flexible than ad-hoc SQL statements.
• Increased maintenance: Stored procedures need to be maintained when your database schema changes.

Overall, stored procedures can be a valuable tool for improving the performance, security, and maintainability of your SQL Server applications. However, it is important to weigh the benefits and drawbacks of using stored procedures before deciding whether to use them in your application. Here are some additional tips for using stored procedures to improve performance:

• Use parameterized queries to avoid parameter sniffing.
• Use local variables to avoid unnecessary data transfer.
• Use WITH (RECOMPILE) to force the query plan to be recompiled each time the procedure is executed.
• Use sp_executesql to execute dynamic SQL statements.
• Monitor the performance of your stored procedures and identify any bottlenecks.

By following these tips, you can ensure that your stored procedures are running as efficiently as possible.

Why does a stored procedure execute faster than other Transact-SQL statements?
SQL Server compresses and optimizes all the Transact-SQL code within a stored procedure. Then the stored procedure, along with its execution plan, is stored for future use. The execution plan, also known as a query plan, is SQL Server’s predetermined plan for which index(es) it will use, as well as the internal path that SQL Server will take to execute the Transact-SQL code. The execution plan is generated the first time the stored procedure is executed, rather than when the stored procedure is compiled. Stored procedures can call other stored procedures, which can help make your applications very modular. Modular applications are beneficial because each module performs a single distinct unit of work, allowing other applications, or even parts of applications, to use only those units of work that are necessary.

Stored procedures are often favored over dynamic Transact-SQL code (which is sent to SQL server at runtime) because of the following reasons:

1. They are faster because the query plan is predetermined.
2. They encapsulate very complex procedures.
3. They offer a way to reuse code.
4. They segregate development tasks for multiple developers.

They allow for easy security administering.

There are three stored procedure types:

1. User-defined stored procedures automate common Transact-SQL transactions and are created and used by the SQL programmer.
2. System stored procedures provide important system maintenance functionality and are preinstalled with SQL Server 2012.
3. Extended stored procedures reside in an external Dynamic Link Library (DLL) and allow you to access functionality that does not natively exist in SQL Server 2000.

In the next lesson, you will learn how to enforce business rules with stored procedures.