Question

In SQL, which of the following statements about the stored procedures subroutine are correct
1. Stored procedures are executable server-side routines
2. Stored procedures can be used as a security layer
3. Stored procedures cannot be used to perform input validation
4. Stored procedures save CPU resources by re-using previously cached execution plans

Answer 1

4. Stored procedures save CPU resources by re-using previously cached execution plans

Explanation:

The correct answer is option 4 Stored procedures save CPU resources by re-using previously cache execution plans.

The biggest performance which is built-in SQL Server is the stored  procedure.  SQL Server has the ability to cache the execution plan to be  re-used at a later time. SQL Server 2000 has improved vastly in  caching execution plans for ad-hoc SQL queries, it is still handling stored  procedures best because they are the database objects that can be referred  to definitively.