SQL Server: what will trigger execution plan?

If I have statement

DECLARE @i INT;
DECLARE @d NUMERIC(9,3);
SET @i = 123;
SET @d = @i;
SEL开发者_StackOverflow社区ECT @d;

and I include actual execution plan and run this query, I don't get an execution plan. Will the query trigger execution plan only when there is FROM statement in the batch?

The simple answer is you don't get execution plans without table access.

Execution plans are what the optimiser produces: it work out the best way to satisfy the query based on indexes, statistics, etc.

What you have above is trivial and has no table access. Why do you need a plan?

Edit:

A derived table is table access as per Lucero's example in comments

Edit 2:

"Trivial" table access gives constant scans, not real scans or seeks:

• SELECT * FROM sys.tables WHERE 1=0
• Lucero's examples in comments

What you mean by what will trigger execution plan? Also I didn't understand I include actual execution plan and run this query, I don't get an execution plan. Hope this link may help you.

1. SQL Tuning Tutorial - Understanding a Database Execution Plan (1)

I would assume that a query plan is generated whenever a set-based operation needs to be performed.

Yes you need a from clause.

You can do like this

declare @i int
declare @d numeric(9,3)
set @i = 123

select @d = @i
from (select 1) as x(x)

select @d

And in the execution plan you see this

<ScalarOperator ScalarString="CONVERT_IMPLICIT(numeric(9,3),[@i],0)">