Perhaps the single most important SQL Server application development Best Practice is the consistent use of parameters in application code.  Parameterized database calls are more secure, easier to program and promote query plan reuse.  Yet I continue to see code with SQL statements constructed by concatenating literal strings with variable values.  I think this is largely out of ignorance rather than design so I decided to show how easy it is for .NET application developers to use parameters and peek into the internals to show why parameters are so important.

How to Use Parameters

Below is a C# snippet of a SqlClient query built and executed using variable concatenation.  This is an example of how not to supply variable values to a SQL query.

Here is a parameterized equivalent of the same query.  The only changes are the parameter token in the query string and that the parameter value was added to the parameter collection instead of embedding in the query text:

So all an application developer needs to do is specify parameter tokens in a query string and add values to the parameter collection.  In the case of a parameterized stored procedure call, no tokens are necessary; simply specify the procedure name as the CommandText, specify CommandType.StoredProcedure and add parameters to the collection:

Parameters Under the Covers

The real differences between a parameterized and non-parameterized statement can be gleaned from a SQL and network trace.  Here is the associated trace event captured with SQL Profiler of the non-parameterized code example:

Note the “SQL:BatchCompleted” event.  The SqlClient API simply sent the query to SQL Server as a batch (text containing one or more SQL statements) because the SqlCommand.Parameters collection was empty.

Digging deeper with a tool like Microsoft Network Monitor, we can see the TDS (Tabular Data Stream) protocol data that SqlClient sent to SQL Server for the request:

The TDS packet contains the entire Unicode SQL statement string, including the text value of 1 (Unicode character 0x0031, boxed in blue) that the application passed in the CommandText.

Now let’s take a look at the parameterized query trace event:

The fact that the EventClass shows “RPC:Completed” event instead of “SQL:BatchCompleted” indicates that SqlClient didn’t really wrap the query in sp_executesql to achieve parameterization like the TextData seems to show.  A RPC (Remote Procedure Call) is a specialized type of SQL request in which parameter values are passed separately from the SQL statement in the TDS request and in native form.  The SqlClient API knew to use a RPC instead of a batch because the non-empty parameters collection.

We can see from the dump of the TDS packet that it contains no reference to sp_executesql nor does it contain the string value 1 for the parameter value that the Profiler trace showed (see SQL Trace Parameter values are not always as they seem for more info).

The important point with the RPC request is that the value 1 (binary integer value, boxed in blue) was passed separately from the query text.  This fact has a number of important implications for security, performance and programming. The difference between the TDS packets shows the essence of parameters in communication between the application code and SQL Server. 

Use Parameters for Security

If you don’t know what SQL injection is, Google is your friend.  Basically, injection vulnerability allows a user to manipulate the intended SQL by supplying mal-formed values.  Injection can allow a malicious user to retrieve sensitive data or change database information, limited only by the database security context.

Parameterized calls protect against SQL injection because the executed SQL statement is constant regardless of the supplied values.  As long as no dynamic SQL is constructed, the intended SQL statement cannot be manipulated.  An application developer only needs to ensure that the desired parameter values can be passed (e.g. key value from session state instead of from http query string) to guard against unintended data disclosure or manipulation.  Using stored procedures (with CommandType.StoredProcedure) always results in an RPC call and provides an additional security layer.

Use Parameters for Performance

Parameterized statements are cached to facilitate reuse and avoid compilation overhead, which is especially important in high-volume OLTP environments.  Furthermore, using stored procedures further promotes caching and reuse. 

The only downside to parameters from a performance perspective is that a given plan may be optimal for some values but not others.  In cases where sub-optimal cached plans are an issue, techniques such as RECOMPILE hints or compromise plan guides can help avoid a sub-optimal execution plans due to varying parameter values.  See article Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 for more information.

Use Parameters for Cleaner Code

Parameters are easier to program and provide cleaner code because there is no need to escape (double-up) quotes, format datetime strings or use “.” as a decimal separator.   Remember that the API takes care of passing parameter values in native form so string formatting is not needed when parameters are used.