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)">