Bind Variable SQL Server with Dot Net

If you've been developing applications on SQL Server for while, you've no doubt come across the concept of  "Bind Variable". Bind variables are one of those Dot Net concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and you need to alter your programming style to use them.

When you use SQL to communicate data between your web application and a database, you have the option to include the literal data in an SQL statement or use bind variables. Bind variables are placeholders for actual values in SQL statements. Writing SQL statements with bind variables rather than substitution variables or literals minimizes processing time and can improve application performance by 20 to 30 percent. Using bind variables can also help prevent an SQL injection attack.
This article compares the performance and security benefits of using bind variables (also known as bind parameters or dynamic parameters) rather than substitution variables or literals in SQL statements. I briefly introduce bind variables, then demonstrate how they're used in SQL statements and show the resulting performance improvement. I also show you how to use bind variables to effectively deter an SQL injection attack in a sample Java application.

Overview of bind variables
A bind variable consists of a variable indicated by a placeholder character such as a question mark (?), :name, or @name. The placeholder character depends on the SQL database server that you use. You provide the actual value of the placeholder at runtime, just before the SQL statement is executed.

How bind variables improve application performance
In most relational databases, an SQL statement is processed in three steps:

1. Parsing the SQL statement: Verifies the SQL statement syntax and access rights and builds the best (optimized) execution plan for the SQL statement. Placeholder variables are not known at this point.
2. Binding variables: Where the API provides the actual values for placeholders.
3. Execution: Done with the selected execution plan and actual value of the placeholder variables.

Each time an SQL statement is sent to a database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found, the database performs a hard parse of that statement. If a matching statement is found, then the database initiates a soft parse.

• In a hard parse, the SQL statement needs to be parsed, checked for syntax errors, checked for correctness of table names and column names, and optimized to find the best execution plan.
• In a soft parse, the SQL statement already exists in a shared pool, so very little processing is required for access rights and session verification.

Using bind variables enables soft parsing, which means that less processing time is spent on choosing an optimized execution plan. Information about how to process the SQL statement has been saved, along with the SQL statement itself, in a shared pool.

Sample :

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;