SQL Server Stored Procedures and advanced applications

 

Today, we will mainly discuss the application and Optimization Solutions of SQL Server Stored Procedures.

Stored Procedure: As I wrote earlierArticleAn overview of the stored procedure is provided. Today, let's take a look at the advanced applications of stored procedures.

First, let's take a look at what the stored procedure can contain:

It can be seen that the Stored Procedure contains not only a single SELECT statement, but also select statement blocks, such as case when. It can also contain logical control statements, such as if-Else.

Note: The stored procedure can also contain insert, update, and delete statements.

Next, let's take a look at the usage of the Extended Stored Procedure xp_mongoshell.

Case: Enable xp_cmdshell

Enable the Extended Stored Procedure in SQL Server2000:

Use master

Exec sp_addextendedproc xp_cmdshell, 'loglog70. dll'

Go

Enable xp_cmdshell in SQL Server 2005 or SQL Server 2008

Sp_configure 'xp _ Your shell', 1

Go

Reconfigure -- make sp_configure take effect immediately

Go

-- Enable advanced options

Sp_configure 'show advanced options', 1

Go

Reconfigure

Go

Note:There are a total of 36 server options. By default, only 10 of the sp_configure stored procedures are displayed, and the results do not contain advanced options, all new SQL server configuration options will not appear in this streamlined list. However, you can use the show advanced options command parameter to display all options on SQL Server.

Case: Disable/delete xp_cmdshell

SQL Server 2000 Delete Extended Stored Procedures

-- Delete the Extended Stored Procedure

Use master

Exec sp_dropextendedproc 'xp _ export shell'

Go

How to Improve the Performance of Stored Procedures

01, use set nocount on

Syntax
Set nocount {on | off}

Note
When set nocount is on, no count is returned (indicating the number of rows affected by the transact-SQL statement ). When set nocount is off, return the count.

When set nocount is on, the done_in_proc information of each statement in the stored procedure is not sent to the client. When you use a utility provided by Microsoft SQL server to execute a query) "Nn rows affected" is not displayed in the query results at the end ".

02. Reduce optional parameters.

03. Optimize SQL statements

001. Avoid frequent access to the same or multiple tables.

002. Avoid a large number of transaction operations as much as possible

003. Avoid using cursors whenever possible

004. Note the where statement syntax.

The order of statements must be considered. The order before and after condition clauses should be determined based on the index order and range, and the field order should be consistent with the index order as much as possible, with the range from large to small.

005. Use exists instead of select count () to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

006. Note the data types connected between tables.

007, write DDL first, then DML

008, rational use of Indexes

Pay attention to index maintenance, re-indexing regularly, and re-compiling the stored procedure.

, Reasonable use of tempdb system tables

I. Avoid using distinct, order by, and Group
By, having, and join statements

Ii. Avoid frequent creation and deletion of temporary tables

III. The data inserted in the temporary table is too large. You can use select into instead of create table.

Iv. When a temporary table is used, it must be explicitly deleted at the end of the stored procedure.

V. Avoid using large temporary tables and other big data tables for link query and modification.

Use of sqlprofiler

Sqlprofiler can detect the execution of any SQL Server statement and help us analyze the final execution process of the SQL statement.

Usage: Open the tool → SQL Server Profiler as shown in Figure

Click Connect. The page is displayed.

Click to run

 

This interface allows you to view SQL Server trace information.

 

I have discussed this for the time being. I hope it will help you!