A review of stored procedures for. NET Developers

This article applies to:

Microsoft®sql Server™

Microsoft Visual studio®.net 2003

Transact-SQL (T-SQL) Language

Summary

Briefly describe the stored procedures in the Microsoft SQL Server database for developers who use the. NET programming language primarily in their work. Uncover the pros and cons of using stored procedures, and briefly describe some of the tools and better practices that are available in Visual Studio.NET 2003 that can help you get started easily.

Preface

The main purpose of this article is to describe some of the key elements of encapsulating T-SQL statements into stored procedures, depending on the needs of your application, so that you can make informed decisions when applying them to your system. I'll introduce some tools and better practices for developers who want to take advantage of them in. NET Applications.

I am well aware that I am now involved in a struggle that resembles a different religion, a struggle over whether the logic of the transaction should be based only on the middle tier or just on the data tier, or whether it is simply writing query code in a program or using a stored procedure. Of course, both of these methods have pros and cons. It is important that we consider what is important to our program and the system environment. So, let's look at what the stored procedure is, and consider why we use it to encapsulate T-SQL statements.

Why do I consider stored procedures?

You may often use SqlCommand objects and write T-SQL statements in your programs to perform data operations, but you never think about finding a better place to store these T-SQL statements, rather than mixing them with your data access code. Perhaps over time, your application's functionality is growing, resulting in many complex T-SQL statement codes. Stored procedures are a better way to encapsulate these complex statements.

Perhaps most people know something about stored procedures, but for those who don't know it, a stored procedure is a set of many predefined T-SQL statements that are stored in a database as a separate unit of code. You can pass the Run-time information to it through the input parameters, or you can get the return data through the result set or the output parameter. When the stored procedure is run for the first time, it is compiled. It produces an execution sequence, essentially a record, that records the execution steps of a T-SQL statement that was specified in the stored procedure to obtain the results. The execution sequence is then saved in the cache for later use. This improves the performance of the stored procedure, because when it is executed again, the stored procedure does not need to parse the code to perform the task, but rather simply shifts to the execution sequence saved in the cache. The contents of the cache are valid before SQL Server restarts and during the lifetime of the memory, and its memory lifetime depends on its minimum memory consumption.

Performance

In queries, the sequence of execution in these caches can improve the performance of the stored procedure. However, in the last two versions of SQL Server, all the T-SQL statement batches are stored in the cache as a sequence of execution, regardless of whether it exists in the stored procedure. As a result, improvements in performance based on this feature are no longer a selling point for stored procedures. Any T-SQL batch with static (infrequently changing) syntax is frequently committed to prevent its execution sequence in the cache from disappearing because it exceeds the memory lifecycle, which can also achieve high performance. Note that "Static" is critical here, because even insignificant changes, such as annotation changes, result in no matching sequences of execution in the cache. This also leads to the inability to reuse the execution sequence.

However, the stored procedure still has its advantages, which can be used to reduce the amount of data transferred to the network. You only need to send the following instructions:

EXECUTE stored_proc_name

You can easily perform a series of complex operations, rather than traditional, lengthy T-SQL statements. A well-designed stored procedure can significantly reduce the round-trip traffic between the client and the server, and can even be compressed to a single call.

In addition, using Remote Call (RPC) server-side stored procedures can improve performance by increasing the reusability of the execution sequence. When you use a SqlCommand object that is CommandType as StoredProcedure, the stored procedure is executed via a remote call (RPC). The remote call arranges the parameters and invokes the server-side stored procedure, which makes it easy for the server engine to find a matching execution sequence and to easily provide the updated parameter values. The last thing to consider is whether you fully develop the power of T-SQL when you are ready to apply stored procedures to improve the performance of your program. Consider how you intend to operate the data.

1. Are you doing some operations based on datasets, or are some other T-SQL operations that have been supported? If so, the stored procedure is a good choice, although it can be done directly by writing query statements.

2, are you doing some data-based operations, or complex string operations? If so, then you can reconsider dealing with T-SQL statements directly, rather than stored procedures. This is the case, at least before the latest Yukon release, and before it is reasonably integrated with the common language runtime.

Maintainability and common extraction

The second potential benefit to consider is maintainability. Ideally your database schema will not change and your transaction rules will not change, but in reality these are frequent occurrences. For example, when you need to add a x,y,z table to a new promotional campaign, you can simply include the data in the process by modifying the stored procedure without needing to go around looking for a modified statement in your program code. Modifying the code in the stored procedure is transparent to the application, and you can still get the same sales information, although the internal implementation of the stored procedure has changed. Directly modifying the stored procedure is more time-saving than if you are modifying the existing code to test and configure the program.

Also, by extracting some of the same implementations and saving them in stored procedures, the application needs to take the same action when it wants to access data through these methods. You don't have to maintain the same code that is scattered in many places, and ensure that your users have consistent information.

Another good thing about maintenance is that keeping T-SQL statements in stored procedures is a good way to control the version. You can use scripts that create and modify stored procedures to do versioning, just as you would control the version of the source code module. By using the Visual SourceSafe and some other version control tools provided by Microsoft, you can easily reference and revert to the old version.

Make no mistake, using stored procedures to improve maintainability does not prevent the need to modify the data schema and transaction rules. When these changes are too large, you need to change the stored procedure's input parameters and return values, and modify your foreground program code such as adding parameters, updating GetValue () function calls, and so on.

Another issue to consider is that using stored procedures to encapsulate transaction logic limits the portability of your application and is tied to SQL Server. If portability is important to your system environment, it is a good choice to encapsulate transaction logic in a middle tier unrelated to the relational database.