TSQL - Execution

Batch is one or more SQL server commands that are dispatched and executed together.

Command Line Execution Steps;

Step1: Parase commands and create the sequence tree

The command parser checks for proper syntax and translates the Transact-SQL commands into an internal format that can be operated on. The internal format is known as a sequence tree or query tree. The command parser handles these language events.

Step2: Compile the batch

An execution plan is generated from the sequence tree. The entire batch is compiled, queries are optimized, and security is checked. The execution plan contains the necessary steps to check and constrains that exist. If an after trigger exists for any data modification statement, the call to that trigger is appended to the execution plan for the statement.

Step3: Execute

During execution, each step of the execution plan is dispatched serially to a manager that's responsible for carrying out that type of command. A statement with an explicit BEGIN TRAN interacts directly with the transaction manager.

Step4: Recompile Execution Plans

It should be clear that the SQL code for routines persists in the database but execution plans don't. Execution plans are cached memory. But sometimes they can be invalidated and a new generated.

We can use GO or EXEC command to execute the batch files