forum

Home / DeveloperSection / Forums / SQL Server: what will trigger execution plan?

Ask Question

SQL Server: what will trigger execution plan?

Pravesh Singh198708-May-2013
Hi Everyone!

If I have statement

DECLARE @i INT;
DECLARE @d NUMERIC(9,3);
SET @i = 123;
SET @d = @i;
SELECT @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?

Thanks in advance! 
mssql servermssql server 
Updated on 08-May-2013
Pravesh Singh

Pravesh Singh

Follow
Student

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

08-May-2013
Hi Pravesh!

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

Liked By