SQL Server: what will trigger execution plan?

Posted by  Pravesh Singh
 1793  View(s)
Rate this:
Hi Everyone!

If I have statement

SET @i = 123;
SET @d = @i;

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. 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?


    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

      Modified On Mar-30-2018 12:44:32 AM