introduction
This article is intended to introduce the main advantages and disadvantages of the Transact-SQL (T-SQL) required to use the stored procedure package so that you can make wise decisions on how to use the stored procedure in your environment. For those who can use the stored procedures, this article also describes tools and best practices related to the use of stored procedures in .NET applications.
In this regard, everyone's opinions are inconsistent, and some people think that business logic should only be encoded into the intermediate or database; some people think that the inline query is the only choice; some people think that the stored procedure is applied to any situation. All of these methods have advantages and disadvantages. It is important to consider what is critical to your applications and the environment. Therefore, let us understand the concept of stored procedures and consider using them to encapsulate some of the reasons for T-SQL code.
Back to top
Reasons for considering the use of stored procedures
Maybe you have written T-SQL using the SQLCommand object, but never considered whether there is a better location than incoming it into the data access code. Since the application adds some functions over time, it may contain some complex T-SQL process code inside. The stored procedure provides a replacement location for the package this code.
Most people may have an understanding of the stored procedure, but for those who do not understand the stored procedures, the stored procedure refers to a T-SQL statement stored in the database as a single code unit. You can use the input parameters to pass to runtime information, and retrieve data as the result set or output parameters. The stored procedure will be compiled during the first time. This will generate an execution plan - actually a record of the steps that Microsoft® SQL ServerTM gets the results you must take in the stored procedure and must take on the steps specified by T-SQL. Then, the execution plan is cached in memory for later use. This improves the performance of the stored procedure, because SQL Server does not need to re-analyze it for determining how to handle the code, but only reference the cache scheme. This cached plan has been available until SQL Server restarts, or overflows memory due to low usage.
performance
The cached execution plan has made the stored procedure more performance advantage than queries. But for several latest versions of SQL Server, the execution plan has been cached for all T-SQL batch, regardless of whether they are in the stored procedure. Therefore, performance based on this function is no longer the selling point of the stored procedure. Any T-SQL batch that uses still syntax and the submission frequency is sufficient to prevent the T-SQL batch of the program overflowing memory will gain the same performance benefits. The "Static" section is the key; any changes, even if you have irrelevant changes like adding comments, it will also result in a plan to match the cache, so that the plan will not be repeatedly used.
However, when the storage procedure can be used to reduce network traffic, they can still provide performance benefits. You only need to send an Execute Stored_Proc_name statement in the network, not the entire T-SQL routine, which can be widely used in complex operations. A well-designed stored procedure can simplify many round-trip processes between the client and the server into a single call.
In addition, the use of stored procedures enables you to enhance reuse of the execution plan, thereby improving performance by using a remote procedure call (RPC) processing server. When using StoredProcedure's SQLCommand.commandType, the stored procedure is performed via RPC. RPC package parameters and call server-side processes allow engines to easily find matching execution plans and simply insert update parameter values.
Considering the use of stored procedures to improve performance, it is finally considered to take advantage of T-SQL. Consider how to handle data.
• Do you want to use a collection-based operation or other operations that fully support in T-SQL? Then the stored procedure is a choice, and the inline query can also be used. • Do you try to perform row-based operations, or complex string processing? Then you may have to reconsider this process in T-SQL, which does not include the use of stored procedures, at least to YUKON release and public language run library (CLR) integration can only be used. Maintainability and abstraction
Another potential advantage to consider is maintainability. Ideally, the database architecture does not change, the business rules are not modified, but in a realistic environment, the situation is completely different. Since the situation, then if the stored procedure can be modified to include data in the new X, Y, and Z tables (add these tables to support new sales activities, this information can be changed in a certain location in the application code. The maintenance may easily be easier to you. Change this information during the stored procedure enables updates to the application transparency - you still return the same sales information, even if the internal implementation of the stored procedure has changed. The update stored procedure is usually less time and effort than changes, tests, and re-deploy assemblies.
In addition, by abstract implementation and this code is saved in the stored procedure, any applications that need to access the data can acquire consistent data. You don't have to maintain the same code in multiple locations, users can get consistent information.
Another maintenanceability advantage that stores T-SQL during the stored procedure is better version control. You can control the script that created and modify the stored procedure, as you can control any other source code module. By using Microsoft Visual SourceSafe® or some other source code control tool, you can easily return to or reference the old version of the stored procedure.
It should be noted when using the stored procedure to improve maintainability, they can't prevent you from making all possible changes to architecture and rules. If the change range is large to the need to change the parameters of the input stored procedure, or if you want to change the data returned by it, you still need to update the code in the program set to add parameters, update getValue () call, and so on.
Another problem to note is that since the stored procedure binds the application to SQL Server, the use of stored procedure package business logic will limit the portability of the application. If the application's portability is very important in your environment, you may be a better choice in an intermediate layer that is not specific to RDBMS.
safety
Considering the ultimate cause of the stored procedure is that they can be used to enhance security.
To manage users access to information, they can provide access to specific data by granting access to users (rather than basic tables). You can see the stored procedure as a SQL Server view (if you are familiar with them), unless the stored procedure accepts the user's input to dynamically change the displayed data.
The stored procedure can also help you solve the code security problem. They can prevent certain types of SQL insert attacks - mainly some of the use of operators (such as and or but) to attach commands to valid input parameter values. When the application is attacked, the stored procedure can also hide the implementation of business rules. This is very important for companies that regard such information as intellectual property.
In addition, using the stored procedure allows you to specify the data type of the stored procedure parameter using the SQLParameter class provided in ADO.NET. This provides a simple way to verify the value type provided by the user (as part of the deep defensive policy). In terms of narrowing the range of acceptable user input, the parameters are useful in the inline query as used in the stored procedure.
It is worth noting that poor security or coding practices will still be attacked when using stored procedures. Creating and assigning SQL Server Roles If it is not noted, it will cause people to access data that should not be seen. At the same time, if you think that the stored procedure can prevent all SQL insertion code attacks (for example, attach the Data Operation Language (DML) to the input parameters, the consequences will be the same. In addition, whether the T-SQL is located in the code or in the stored procedure, the use of the parameters is not very different. The data provided by all users (especially text data) should be attached before passing to the database.
Does the stored procedure apply to me?
Perhaps it is suitable. Let us generalize their advantages:
• By reducing network traffic, highlighting, providing single-point maintenance • Abstract business rules to ensure consistency and security • By minimizing certain forms of attacks, supporting security • Supporting
If your environment allows the benefits of the stored procedure (as mentioned above), they are highly recommended. For improved data, they provide a good tool. On the other hand, if there is portability in your environment, a large number of factors that weaken these advantages are weakened using non-T-SQL-friendly processes or unstable database architecture, and you may have to consider other methods.
Another thing to note is the number of T-SQL professionals they have inside the organization. Do you have enough T-SQL knowledge? Are you willing to learn? Or do you have DBA or suitable person to write a stored procedure? The more T-SQL knowledge of the master, the better the stored procedure, the easier it will be maintained. For example, T-SQL is primarily used for a collection of operations instead of row based operations. Depending on the cursor (because they prompted the data set) will result in a reduction in performance. If you don't know T-SQL, please use this article as a learning opportunity. No matter where you use it, the knowledge introduced in this article will improve your code.
Therefore, if you think that the stored procedure adds special effects to your application, please continue to read this article. We will review some tools to simplify the stored procedures and learn the best practices for creating stored procedures.
Back to top
Visual Studio .Net Tool
Microsoft Visual Studio® .NET provides some tools that allow you to view and operate SQL Server stored procedures (and other database objects). Let us briefly introduce the functions you may expect.
View stored procedure
You can use the Server Explorer to view an existing stored procedure to see the parameters they need or view their internal implementation. If you have been connected to a server installed SQL Server, you can sequently expand "ServerName", "SQL Server", "Northwind", "Stored", and expand "CustOrDersDetail". The resource manager will display any parameters required for the stored procedure and any column thereof. If you check out any properties of any items, you will find that the data type is represented as an ADO type. The framework document provides a simple mapping between these types and .NET types. Of course, when using parameters in the ADO.NET code, you may have to represent their data type as members of the SqldbType enumeration. You will see mappings between the .NET type and the SQLDBTYPE type. If you double-click the stored procedure, Visual Studio will open it in the SQL editor, which shows the color coding and all content that can be easily viewed. Note that it does not display the CREATE Procedure syntax (this syntax is actually existing in the database), because it assumes that you want to modify the stored procedure, so ALTER Procedure syntax is provided. Create and modify stored procedures
Do you have permissions for creating stored procedures in the corresponding database? If not, this permission should be obtained before creating and modifying the stored procedure. If you need help, please contact the database administrator.
You can create a new stored procedure, right-click the "Stored Procedure" node (or any other stored procedure) in the Server Explorer (or any other stored procedure ". A new window will be opened in the SQL editor, which provides some outline Create Procedure syntax to help you start writing code. You can then type code in the main body of the stored procedure. Modifying the stored procedure is the same as the starting operation of the stored procedure: Find the stored procedure in the Server Explorer and open it.
If you need help when you create a query for stored procedures, right-click on the stored procedure window and select "Insert SQL". Alternatively, you can also select a T-SQL block and right-click it and select "Design SQL Block". The "Query Builder" window is opened by any of the above operations, which provides a graphical interface for building or modifying the T-SQL statement. After completing, T-SQL can be cut and pasted into the stored procedure.
Unfortunately, there is no IntelliSense in the editor, so I will open the "SQL Server Book" for reference. When trying to save the stored procedure, the system will prompt you any syntax errors that need to be corrected. Note that only the syntax error can be saved, and the stored procedure can be saved, so before starting the encoding, it should ensure that the time is completed. You can make initial encoding and testing at the SQL Server query analyzer at any time, but this is not within the scope of this article.
After completing the required code, you can test the stored procedure by clicking right click and selects the Run Store option.
Back to top
Entry tips
If you want to start creating a stored procedure for use with your application, you should remember the following tips so that both work and work well.
Use Set NoCount on
By default, the stored procedure will return the number of rows that affect each statement during the process. If you don't need to use this information in your application (most applications are not needed), use the Set NoCount ON statement during the store to terminate the behavior. Delete one or more round-trip processes between the client and the server depending on the number of statements that affect the rows included in the stored procedure. Although this is not a big problem, it can have a negative impact on the performance of high flow applications.
Create Procedure Test_myStoredProc @ param1 intas
Set nocount on
Do not use sp_ prefix
SP_ prefix is reserved for the system stored procedure. The database engine will always first look for stored procedures with this prefix in the primary database. This means that when the engine first checks the primary database, then check the database of the stored procedure, it will take longer to complete the inspection process. Moreover, if there is a system stored procedure that happens to have the same name, your process will not be processed at all.
Try to use optional parameters
Please consider it before frequently using optional parameters. By performing additional work, it will easily affect performance, and it is not required to work according to the set parameter set to arbitrarily specify. You can solve this problem by using conditional coding for each possible parameters, but this is a cost time and increase the chance.
Use Output parameters in a possible situation
By using the OUTPUT parameter return scalar data, the speed can be slightly improved and a small amount of processing power is saved. In the case where the application needs to return a single value, try this method instead of setting the result set. In appropriate, the OUTPUT parameter can also be used to return the cursor, but we will introduce the cursor processing and collection based processing in subsequent articles in theoretical differences.
Provide return value
Use the return value of the stored procedure to return the processing status information to the application that is called. In your development group, a set of return values and their meaning are standardized, and these values are used together. This makes it easier to process errors in the calling application and provide useful information about the problem.
First use DDL, then use DML
When the DML statement is placed after the Data Definition Language (DDL) statement (at this point DML will refer any object of DDL modification), SQL Server recompores the stored procedure. This happens because SQL Server needs to consider the changes made by DDL to the object by DDL in order to create a plan for DML. If you pay attention to all DDLs at the beginning of the stored procedure, it only needs to be recompiled once. If you use the DDL and DML statements, the forced stored procedure will be re-compiled multiple times, which will have a negative impact on performance.
Always use comments
You may not always maintain this code. But other people may want to understand its use in the future. 'NUFF has said this.
