Cracking Query Execution - II: Why Actual and Estimated Query Execution Plan may differ

Generally there is no difference between Actual Execution plan and Estimated plan and Query is executed the way Estimated Plan outlines.However, circumstances can arise that can cause Execution Plan to change:

Obsolete Statistics:
The main cause of difference between the plans is difference between the statistics and the actual data.This generally occurs over time as data is added and deleted. This causes the key values that define the index to change,or their distribution to change.

Estimated Plan becomes Invalid:
In some instances, SQL Server Engine doesn't even generate Estimated execution plan, like
CREATE TABLE tblAbc
(
Col1 INT,
Col2 INT);

INSERT INTO tblAnd VALUES ( 1,2);

This will through error as
Msg 208, Level 16, State 1, Line 6
Invalid object name 'tblAnd'.

As table tblAbc doesn't exists in database yet, so it cant create a plan for it.

When Parallelism is required:
When a plan meets a threshold for parallelism two plans are created. Which plan is actually executed is up to the query engine.