What are the ways available in SQL Server to execute SQL statements?SQL Server uses different ways to execute SQL statements which are listed below:
- Single SQL statement processing.
- Batch processing.
- Stored procedure and trigger execution.
- Execution plan caching and reuse.
- Parallel query processing.
Single SQL statement:
- It is the basic way of processing the statement.
- It is used to process a single SELECT statement.
- It is a collection of one or more SQL statements which is sent by the client in one unit.
- Each batch is compiled into a single execution plan.
Stored procedure and trigger execution:
- When a stored procedure or trigger is first executed, the source is compiled into an execution plan.
- It allows for greater reuse of the stored procedure and trigger execution plans.
- These plans are easily reused.
Execution plan caching and reuse:
- Execution plans have two main components:
1. Query plan: It is referred to as the query plan. It covers all the parallel executions.
2. Execution context: It is referred as the execution context. It is reinitialized with the context for the new user.
- It has a pool of memory which is used to store both execution plans and data buffers.
Parallel query processing:
- SQL server performs operations in parallel by using several operating system threads.
- The operation can be completed quickly and efficiently.