Home > DeveloperSection > Forums > SQL Server: what will trigger execution plan?
Pravesh Singh

Total Post:411

Points:2881
Posted on    May-08-2013 1:15 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 954  View(s)
Rate this:
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! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-08-2013 8:16 AM

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


Don't want to miss updates? Please click the below button!

Follow MindStick