Estimated Execution Plan is a plan that SQL Server generates without executing the query, hence the name Estimated. It is similar to when you need to build a house and ask the builder for an estimated price. The builder does not build the house, however, comes up with an approimate price. SQL Server is also similar, you can ask SQL Server for an estimated execution plan. Then, SQL Server tries to build an estimate of the execution without executing the query.
On the other hand, Actual Execution Plan is the plan that is generated when SQL Server really executes a query. Actual Execution Plan specifies the route that SQL Server
actually went through to execute the query.
When you ask SQL Server for the Actual Execution Plan, it is similar to ask the builder to build the house and come up with the actual price. SQL Server, in this case, actually executes the query and outputs the path it went throught to execute the query.
So, another big difference between the Estimated and Actual Execution Plans is about query execution. In case of Estimated Execution Plan,
the query is not run at all and is just SQL Server's estimation of the query. However, in case of Actual Execution Plan, the query
is really run and SQL Server provides the details about the query execution.
The two types of execution plans above have a lot in common in terms of the structure and appearance. However, it IS possible that the two types of Execution Plans differ.
Estimated Execution plan is very useful for optimizing queries that you really cannot run on the database. Imagine you need to check an UPDATE statement for performance. You obviously cannot run the UPDATE statement on production server just for diagnosis purposes. In that case, you can diagnose using the Estimated Execution Plan, as it does not execute and consequently does not touch the data.