Microsoft® SQL Server™ version 7.0 changes the way stored procedures and triggers are executed. SQL Server 7.0 also introduces changes in the way all execution plans are managed that gives many batches the same performance benefits that were only available to stored procedures in earlier versions of SQL Server.
SQL Server 7.0 stores only the source for stored procedures and triggers. When a stored procedure or trigger is first executed, the source is compiled into an execution plan. If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. If the plan has aged out of memory, a new plan is built. This process is similar to the process SQL Server 7.0 follows for all SQL statements. The main performance advantage that stored procedures and triggers have in SQL Server 7.0 is that their SQL statements are always the same, therefore the relational engine matches them with any existing execution plans.
Stored procedures had a more pronounced performance advantage over other SQL statements in earlier versions of SQL Server. Earlier versions of SQL Server did not attempt to reuse execution plans for batches that were not stored procedures or triggers. The only way to reuse execution plans was to encode the SQL statements in stored procedures.
The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. This allows for greater reuse of the stored procedure and trigger execution plans. For more information, see Batch Processing.