Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Understanding the Execution Plan [When Table JOIN Occurs Part-III]

DZone's Guide to

Understanding the Execution Plan [When Table JOIN Occurs Part-III]

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

Introduction

Continuing our journey here we see some others operators exist in our execution plan. Here is the query and Execution Plan that we are working from couple of weeks.

Example Query

SELECT e.JobTitle, a.City,
 p.LastName + ', ' + p.FirstNameASEmployeeName

FROM HumanResources.Employee AS e

 INNER JOIN Person.BusinessEntityAddress AS bea

ON e.BusinessEntityID =bea.BusinessEntityID

 INNER JOIN Person.Address a ON bea.AddressID =a.AddressID

 INNER JOIN Person.Person AS p
 ON e.BusinessEntityID = p.BusinessEntityID;

Execution Plan

Compute Scalar

First of all it is not a join operation. As it is covered in our Execution Plan, so we must discuss about it. Here we see the properties of the Compute Scalar.

It is represent a operation named Scalar, generally used for calculation purpose. In our case the alias Employeename = ContactLastname + Conatct.FirstName with comma operators between them. If we look at the property, it is not a 0 cost operators (0.001997).

If we look at the property Expr1008 and click at the ellipsis on the right side of the property page this will open the expression.

Related Reference

Understanding the Execution Plan [ When Table JOIN occurs Part-II ]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table_25.html

Understanding the Execution Plan [ When Table JOIN occurs Part-I ]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html

Understanding of Execution Plan – III - C [ The OPERATORS]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-c.html

Understanding of Execution Plan – III - B [ The OPERATORS ]

http://sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-b.html

Understanding of Execution Plan [What happened When SQL statement Execute]

http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html

Understanding of Execution Plan – II [Reuse of the Execution Plan]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-ii.html

Understanding of Execution Plan – III - A [ The OPERATORS ]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-the.html

Summary

In the next level we have to more discuss about our execution plan. So this series will be continued for some more articles. Please be with us.

Hope you like it.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}