Tips for using stored procedures in SQL Server 2016
Try to avoid using temporary tables and DDL (Data Definition Language) statements
inside your stored procedure.
Using temporary tables or DDL statements inside stored procedure reduces the chance to reuse
the execution plan.
Consider using user-defined table type as a parameter for the stored procedures.
SQL Server 2016 supports a user-defined table type that supports representing table structures
for use as parameters in a stored procedure. So, table-valued parameters can send multiple rows
of data to a SQL Server 2016 statement or stored procedure/function without creating a temporary
table.
Include the SET NOCOUNT ON statement into your stored procedures.
This can reduce network traffic, because your client will not receive the message indicating
the number of rows affected by a Transact-SQL statement.
Consider using natively compiled stored procedures.
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code.
These stored procedures allow for efficient execution of the queries and business logic in the
stored procedure. The difference between interpreted (disk-based) stored procedures and natively
compiled stored procedures is that an interpreted stored procedure is compiled at first execution,
whereas a natively compiled stored procedure is compiled when it is created. With natively
compiled stored procedures, many error conditions can be detected at create time and will
cause creation of the natively compiled stored procedure to fail.
Consider returning the integer value as an RETURN statement instead of an integer value as
part of a recordset.
Though the RETURN statement is generally used for error checking, you can use this statement
to return an integer value for any other reason. Using RETURN statement can boost performance
because SQL Server 2016 will not create a recordset.
Use the sp_audit_write stored procedure to add a user-defined audit event to
the user-defined audit group.
SQL Server 2016 audit specifications support a user-defined audit group. Audited events can be
written to the audit log by using the sp_audit_write stored procedure.
Consider using CLR stored procedures.
In SQL Server 2016 you can use stored procedures written in .NET Framework languages, such as
Visual Basic .NET and Visual C# .NET. You can use CLR stored procedures because .NET languages
support logic and features not available in the Transact-SQL. For example, you can use user-defined
types and aggregates that written in .NET languages to build more complex data types than available
in Transact-SQL.
Use stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure
name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures
can be used to enhance security and conceal underlying data objects also.
Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your
query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so
SQL Server 2016 does not cache a plan for this procedure and the procedure is recompiled at run
time. Using the WITH RECOMPILE option can boost performance if your query will vary each time
it is run from the stored procedure because in this case the wrong execution plan will not be used.
Use the sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends using the temporary stored procedures when connecting to earlier versions
of SQL Server that do not support the reuse of execution plans. Applications connecting to
SQL Server 2016 should use the sp_executesql system stored procedure instead of temporary
stored procedures to have a better chance to reuse the execution plans.
Consider dividing a very large stored procedure into several sub-procedures, and then call
them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes were made to a table or
view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large
number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure.
So, if you break down a very large stored procedure into several sub-procedures, you get
chance that only a single sub-procedure will be recompiled.
Do not use the prefix “sp_” in the stored procedure name if you need to create
a stored procedure to run in a database other than the master database.
The prefix “sp_” is used in the system stored procedures names. Microsoft does not recommend
using the prefix “sp_” in the user-created stored procedure name, because SQL Server always
looks for a stored procedure beginning with “sp_” in the following order: the master database,
the stored procedure based on the fully qualified name provided, the stored procedure using
dbo schema, if one is not specified. So, when you have the stored procedure with the prefix
“sp_” in the database other than master, the master database is always checked first, and
if the user-created stored procedure has the same name as a system stored procedure, the
user-created stored procedure will never be executed.
Consider using LOBs in natively compiled stored procedures.
SQL Server 2016 introduces supporting LOBs as variables and input parameters of the natively
compiled stored procedures. You can also use LOBs as parameters passed into string functions
in a natively compiled procedure.
Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored
procedure instead of the EXECUTE statement improve readability of your code when there are
many parameters are used.
Consider using automatically executed stored procedures.
You can create one or more stored procedures that will be executed automatically when
SQL Server starts. It can be useful to perform the maintenance tasks, or if you need to
have a stored procedure that will run as a background process at all times. You can use
the sp_procoption system stored procedure to mark an existing stored procedure as a
startup procedure.
Note. The automatically executed stored procedure must be created in the master database,
owned by “sa” and cannot have input or output parameters.
Use the SQL:StmtRecompile event class to indicate statement-level recompilations caused
by stored procedures instead of using the SP:Recompile event class.
Microsoft recommends using the SQL:StmtRecompile event class to determine which stored
procedures has been recompiled too often. When you identify these stored procedures,
you can take some correction actions to reduce or eliminate the excessive recompilations.
Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database
name, schema name, and object name. An object name that specifies all four parts is known
as a fully qualified name. Using fully qualified names eliminates any confusion about
which stored procedure you want to run and can boost performance because SQL Server has
a better chance to reuse the stored procedures execution plans.