StaticvsDynamic queries

• kevin_nikolai

  SSCarpal Tunnel

  Points: 4343

  More actions

  November 3, 2008 at 2:18 pm

  #214813

  1. Static query: (Clustered Index Seek cost is 100% - estimated execution plan)

  Select * from tblContract_Instal1

  where Contract_No = 4669

  2. Dynamic query: (Clustered Index Seek cost not shown - estimated execution plan)

  exec ('Select * from tblContract_Instal1

  where Contract_No = 4669')

  Question:

  1. Why is the Clustered Index Seek cost not shown in no.2

  2. Can anyone give me more examples of Dynamic queries.

  Regards

  Kevin

  sonyt65@yahoo.com

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  November 3, 2008 at 8:08 pm

  #893384

  clive (11/3/2008)

  ---

  1. Static query: (Clustered Index Seek cost is 100% - estimated execution plan)

  Select * from tblContract_Instal1

  where Contract_No = 4669

  2. Dynamic query: (Clustered Index Seek cost not shown - estimated execution plan)

  exec ('Select * from tblContract_Instal1

  where Contract_No = 4669')

  Question:

  1. Why is the Clustered Index Seek cost not shown in no.2

  2. Can anyone give me more examples of Dynamic queries.

  Regards

  Kevin

  sonyt65@yahoo.com

  Have you tried the Actual Execution Plan? The reason why the Clustered Index Seek was not shown in #2 is because dynamic SQL is not evaluated in estimated execution plans because it doesn't exist as anything that get's executed until it's actually executed.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)