SQL SERVER: WHAT WILL TRIGGER EXECUTION PLAN?

Pravesh Singh

Total Post:412

Points:2888
Posted by  Pravesh Singh
 1258  View(s)
Ratings:
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! 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL Server: what will trigger execution plan?

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!