Home » Microsoft » MB6-886 » Which action should you take?
You manage a database in an instance of SQL Server 2008. You want to allow users to execute a given query providing different WHERE clause values. You want the solution to provide the best performance possible, but also provide maximum security. Which action should you take?
A. Use string concatenation to build the query string and then issue the EXECUTE statement, passing it the string.
B. Use a Common Table Expression (CTE).
C. Create a parameterized stored procedure that uses sp_executesql.
D. Create a stored procedure that includes the WITH RECOMPILE clause to execute the query.
Correct Answer: C
Explanation/Reference:
In this scenario, you need to dynamically create SQL statements that include different WHERE clause values. To provide the best performance, you should use a parameterized stored procedure so that cached execution plans are more likely to be used. When executing dynamic SQL, you should use the sp_executesql system stored procedure because it minimizes the likelihood of a SQL injection attack.
You should not use a Common Table Expression (CTE). CTEs are used to make Transact-SQL that includes subqueries more readable, or as an alternative to using a view or temporary table. The subquery can be defined once in a CTE definition and then referenced multiple times in the SQL statement following the CTE definition.
You should not use string concatenation to build the query string and then issue the EXECUTE statement, passing it the string. This would increase the likelihood of a SQL injection attack. To minimize the possibility of SQL injection attacks, you should use the sp_executesql system stored procedure to execute dynamic SQL instead of using the EXECUTE statement.
You should not create a stored procedure that includes the WITH RECOMPILE clause to execute the query. The WITH RECOMPILE clause is used to force stored procedure compilation. If you include the WITH RECOMPILE clause when creating a stored procedure, the stored procedure will be recompiled each time it is called, which may decrease performance.
