SQL Server

Tips for using stored procedures in SQL Server 2016

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


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
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.