June 1, 2011

Finding an Expression Definition in a Graphic Execution Plan

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 10:18 pm
Tags: ,

Today on Twitter #SqlHelp, someone asked how to find the definition of an expression in a graphic execution plan. I thought I’d pass along my answer in case it helps someone else.

Consider a very simple query, which includes a scalar operator, such as a SUM():

  1. SELECT ProductID
  2.       , TotalQty = sum(sd.OrderQty)
  3.   FROM AdventureWorks2008R2.Sales.SalesOrderDetail  sd
  4. GROUP BY ProductID

It produces a simple graphical execution plan like this:


Examining the Hash Match operator shows an output named “EXPR1002”. It’s a calculated expression, but what’s its definition?


To find a calculated expression’s definition, select the operator and hit F4 to bring up the Properties window.  The definition is in the Defined Properties entry.  Here, it’s the SUM() of the OrderQuantity.


In a more complex query the output of one operator may be used by another operator, resulting in expressions operating on expressions, so you may need to check more than one step to find the fields involved.


1 Comment »

  1. Interesting.

    I don’t suppose that this would provide any information for FETCH API_CURSOR statements? I’m on vacation so I can’t check myself at the moment.

    Comment by Fred — June 3, 2011 @ 7:58 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: