Design and implementation of SQL Server 2008 stored Procedures

There are two basic types of system stored procedure:
system stored procedure: written in T-SQL language, provided in the master database, most of them use the sp_ prefix. When used in other databases, there is no need to specify the master database as part of their name, typically used in Management tasks
system extended stored procedure: written by unmanaged code (mainly C ++), provided by DLL, most of which use the xp_ prefix, used to extend tasks that cannot be completed using T-SQL
You can also create a user-defined extended stored procedure and then attach it to SQL Server. It is now abandoned because the extended stored procedure is executed on the SQL Server memory space. This is not a safe place to execute code. It is now recommended to use Is managed code stored procedures

The following three types of statements are not allowed to be used in stored procedures:
Create other objects, including AGGREGATE, RULE, DEFAULT, SCHEMA, FUNCTION, TRIGGER, PROCEDURE, VIEW (the four are not allowed ALTER)
Modify SET options related to the query plan, including PARSEONLY, SHOWPLAN_ALL, SHOWPLAN_TEXT, SHOWPLAN_XML
Use the USE statement to modify the database context. You can refer to objects in another database by using the database name, but you are not allowed to use the USE databasename statement to switch the database context.
Creating a stored procedure requires the CREATE PROCEDURE permission of the current database and the ALTER permission of the schema to which the stored procedure belongs. When using the stored procedure, you must ensure that the settings of the database connection are the same as when the stored procedure is created, such as QUOTED_IDENTIFIER, ANSI_NULLS; if the name is created when the stored procedure is created The # prefix is used, indicating that it is a temporary object that will be created in tempdb and will be deleted after the server restarts; using BEGIN ... END is not necessary, but it is good programming practice
When creating a stored procedure, it is very important to use two-part name to refer to the object. If only the name of the table is used and no schema name is specified, SQL Server first looks for the default schema. If it is not found, it looks for the dbo schema, which minimizes The query plan can be reused, because it is not possible to determine which schema table is referenced until the stored procedure is executed. If two-part name is specified, the ambiguity of referencing the table is avoided, and the query plan reused by SQL Server is maximized may
If you only provide the name of the stored procedure when creating the stored procedure, the SQL Server view creates the stored procedure under the default schema, and the location of the faulty procedure creation is vulnerable based on the default schema behavior of the executing user
When executing a stored procedure, if only the name of the stored procedure is specified, SQL Server will sequentially search in the following locations:
If the name of the stored procedure starts with sp_ (user stored procedures are not recommended), search under the sys schema of the master database
Find under the default shema of the user executing the stored procedure
Find under the dbo schema of the current database
When modifying a stored procedure, you cannot modify the type of stored procedure. For example, you cannot modify a stored procedure created using T-SQL into a stored procedure of managed code; the database connection settings will use the settings at the time of modification, not the settings at the time the stored procedure was originally created , So it is very important to ensure that the settings at the time of modification and creation are the same; all the options used during creation (such as WITH ENCRYPTION) must be provided when modifying, otherwise these options will not be retained
When deleting a stored procedure, you need the ALTER permission of the schema to which the stored procedure belongs or the CONTROL permission of the stored procedure itself. To delete the extended stored procedure, use sp_dropextendedproc
Previous versions of SQL Server used sp_depends to return the dependencies between objects. If this stored procedure has problems, it cannot return complete information; now using sys.sql_expression_dependencies, each dependency is one line, and each entity referenced by other entities is one line , sys.dm_sql_referencing_entities each entity referencing another entity is a line
Create stored procedures guidelines:
Use two-part name in the stored procedure, try to specify the full name
Maintain consistency of SET options
Name consistency, using a unified naming convention, do not use sp_ prefix, these stored procedures are used to define system stored procedures
Use @@ nestlevel to view the current nesting level, the maximum value is 32, and calling another stored procedure in a stored procedure is a nesting
Ensure that each stored procedure completes a task
Use WITH ENCRYPTION to encrypt stored procedures, but the shortcomings of encryption here are the same as view encryption, not very robust, there are many third-party tools can be cracked, it is not recommended to use
User-defined functions cannot specify output parameters. Each stored procedure returns an integer value. The default value is 0. It is used to indicate the result value or status code. It can also return business logic related values, but output parameters should be used to indicate business logic related. value
The output parameters of the stored procedure have @ prefix, data type, and you can also specify the default value. When executing the stored procedure, you can specify the parameter value in order, or you can specify the parameter value by name; it is best to verify at the beginning of the stored procedure Legality of input parameters; output parameters are similar to input parameters, and the OUTPUT keyword must be explicitly specified when defining and using output parameters
The execution plan generated for the stored procedure is mostly reused, which is usually the desired behavior; but for different parameter sets, some stored procedures may require completely different execution plans. This type of problem is called parameter sniffing , The solution is as follows:
When defining a stored procedure, add the WITH RECOMPILE option, so that each time it is executed, the stored procedure will be recompiled
When the sp_recompile system stored procedure is called, the passed stored procedure will be marked as illegal and will be recompiled the next time it is executed. You can also pass the name of the table or view to this stored procedure, so that any execution that refers to the object The plan will be recompiled
You can specify the WITH RECOMPILE option during execution, which will recompile the execution plan, but will not store the generated execution plan
OPTIMIZE FOR, this is a query prompt option that allows you to specify values for the parameters when compiling the query, no matter what the parameter value is during actual execution
The execution context is the security context in which the stored procedure is executed. It is used to establish a permission identifier, which is represented by the login token and user token. A security token includes the following:
The principal of a server or database as the main identifier
One or more principals as secondary identification
0 or more authenticators
Privileges and authorities for primary and secondary identification
Login token: verified by the SQL Server service instance, including the primary and secondary logos, regarding server-level permissions and any database-level permissions, the primary logo is the login itself, and the secondary logo includes permissions inherited from rules and groups
User token: verified by a specific database, including the primary and secondary identifiers for database-level permissions. The primary identifier is the database user itself, and the secondary identifier is the permission inherited from the database roles. The user token cannot include members of the server role and is not encouraged Give server-level permissions
The EXECUTE AS statement is used to set the execution context of the stored procedure to rewrite the default security context; supports explicit simulation, using a separate EXECUTE AS statement, and also supports implicit simulation, using the EXECUTE AS statement on modules, in order to To execute on a user, you must have IMPERSONATE permission on that user
EXECUTE AS LOGIN or the module on the server scope uses the EXECUTE AS statement, the scope of which is server-level, that is, after switching the context, resources that simulate the permissions of the logged-in user can be accessed; EXECUTE AS USER or used on the database-level module EXECUTE AS statement, meaning that references to objects outside the scope of the database will return an error
The sys.login_token system view displays all login-related tokens, including the login itself and the token of the user's role; the sys.user_token system view displays user-related tokens
Design and Implementation of SQL Server 2008 Stored Procedure