SQL Server System stored procedures

Stored Procedures in transact-SQL are very similar to methods in Java. They can be called repeatedly. After a stored procedure is executed once, you can cache the statements in the memory and directly use the cached statements during the next execution. This improves the performance of stored procedures.

Concept of Stored Procedure

Stored Procedure procedure is a set of SQL statements for specific functions. Compiled and stored in the database. You can specify the name of the stored procedure and provide parameters for execution.

Stored Procedures can contain logical control statements and data manipulation statements. They can accept parameters, output parameters, return one or more result sets, and return values.

Since the stored procedure is compiled on the database server and stored in the database at the time of creation, the stored procedure runs faster than a single SQL statement block. At the same time, because you only need to provide the stored procedure name and necessary parameter information during the call, it can also reduce network traffic and simple network burden to a certain extent.

1. Advantages of Stored Procedures

A. stored procedures allow standard component programming

After a stored procedure is created, you canProgramIs called and executed multiple times, instead of re-writing the SQL statement of the stored procedure. Database professionals can modify stored procedures at any time,Source codeBut there is no impact, thus greatly improving the program portability.

B. Fast execution of Stored Procedures

If an operation contains a large number of T-SQL statementsCodeThe storage process is much faster than the batch processing. Because the stored procedure is pre-compiled, when a stored procedure is run for the first time, the query optimizer analyzes and optimizes the stored procedure and provides the storage plan that is finally stored in the system table. The T-SQL Statement of batch processing needs to be pre-compiled and optimized every time, so the speed will be slower.

C. reduce network traffic through stored procedures

For the same database object operation, if the T-SQL statements involved in this operation are organized into a stored procedure, when the stored procedure is called on the client, only this call statement is passed in the network; otherwise, multiple SQL statements are passed. This reduces network traffic and network load.

D. stored procedures can be fully utilized as a security mechanism

The system administrator can restrict the permissions of a stored procedure to prevent unauthorized users from accessing data and ensure data security.

 

System stored procedures

A system stored procedure is a stored procedure created by the system. It is designed to conveniently query information from the system table or complete management tasks related to updating database tables or other system management tasks. The system stored procedures are mainly stored in the master database and stored procedures starting with "SP. Although these system stored procedures are in the master database, we can still call the system stored procedures in other databases. Some system stored procedures are automatically created in the current database when a new database is created.

Common system stored procedures include:

 
-- Common system stored procedures
 

ExecSp_databases;// View the database
 

ExecSp_tables;// View the table
 

ExecSp_columns student;// View Columns
 

ExecSp_helpindex student;// View the index
 

ExecSp_helpconstraint student;// Constraints
 

ExecSp_stored_procedures;
 

ExecSp_helptext'SP _ stored_procedures';// View the stored procedure creation and definition statements
 

ExecSp_rename student, stuinfo;// Modify the table, index, and column name
 

ExecSp_renamedb mytempdb, mydb;// Change the Database Name
 

ExecSp_defaultdb'Master','Mydb';// Change the default database for the login name
 

ExecSp_helpdb;// Database Help to query database information
 

ExecSp_helpdb master;
 

-- Rename a table
 

ExecSp_rename'Stu','Stud';Select*FromStud;
 

-- Rename a column
 

ExecSp_rename'Stud. name','Sname','Column';
 

-- Rename an index
 

ExecSp_rename n'Student. idx_cid', N'Idx _ cid', N'Index';
 

-- Query all stored procedures
 

Select*FromSYS. ObjectsWhereType ='P';
 

Select*FromSYS. ObjectsWhereType_descLike '% Pro %' AndNameLike 'SP %';