Consider the following 4-table-join query:

USE Northwind;

SELECT 

S.ShipperID

,S.CompanyName

,O.OrderID

,O.ShippedDate

,EMP.EmployeeID

,EMP.LastName

,O.CustomerID

,C.CompanyName

FROM Shippers AS S

INNER JOIN Orders AS O

ON O.SHIPVIA = S.ShipperID

INNER JOIN Employees AS EMP

ON EMP.EmployeeID = O.EmployeeID

INNER JOIN Customers AS C

ON C.CustomerID = O.CustomerID

ORDER BY S.ShipperID ASC, O.ShippedDate DESC;

Perform the following steps:

a) Display an estimated execution plan (Highlight the query, go to your Query menu, select "Display Estimated Execution Plan")

b) Study the execution plan - write it down, make a screenshot, look up what the icons mean, so that you understand this as much as you can.  

(https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017)

c) Execute the following code to make an index:

CREATE NONCLUSTERED INDEX idxOrdersShipVia

ON [dbo].[Orders] ([ShipVia])

INCLUDE ([OrderID],[CustomerID],[EmployeeID],[ShippedDate])

d) Do step a again, ie., display the estimated execution plan 

e) Discuss below how the index changed (or did not change) the execution plan for the query. Discuss whether or not you 

think this index should be permanently implemented on the Northwind database.

 

Transcribed Image Text:

FK_CustomerCustomerDemo_Customers FK CustomerCustomer Demo Customers 8 Customer D CompanyName: ContactName Contact Title Address City Region PostalCode Country Phone Fax CustomerCustomerDemo 8 Customer D 8 CustomerTypeID CustomerDemographics 8 CustomerTypeID CustomerDesc Order Details 8 Order D 8 ProductID UnitPrice Quantity Discount FK Order Details Orders FK Orders Customers oc Orders 8 Order D Customer D Employee D OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostal Code ShipCountry FK Orders Shippers Shippers 8 ShipperD Company Name Phone koo FK Order Details_Products FK Employees Employees 8 FK Orders Employees co Cu Products 8 ProductID ProductName Supplier D CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued Employees 8 Employee D LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region Postal Code Country HomePhone Extension Photo Notes Reports To PhotoPath seTerritories ooo Territories oo- FK Products Categories FK Products Suppliers Employee Territories 8 Employee D 8 TerritoryID Territories Territory D TerritoryDescription RegionID FK Territories Region Region 8 RegionID RegionDescription -C Categories 8 CategoryID CategoryName Description Picture Suppliers 8 Supplier D CompanyName ContactName Contact Title Address City Region Postal Code Country Phone Fax HomePage FK_CustomerCustomerDemo_Customers FK CustomerCustomer Demo Customers 8 Customer D CompanyName: ContactName Contact Title Address City Region PostalCode Country Phone Fax CustomerCustomerDemo 8 Customer D 8 CustomerTypeID CustomerDemographics 8 CustomerTypeID CustomerDesc Order Details 8 Order D 8 ProductID UnitPrice Quantity Discount FK Order Details Orders FK Orders Customers oc Orders 8 Order D Customer D Employee D OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostal Code ShipCountry FK Orders Shippers Shippers 8 ShipperD Company Name Phone koo FK Order Details_Products FK Employees Employees 8 FK Orders Employees co Cu Products 8 ProductID ProductName Supplier D CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued Employees 8 Employee D LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region Postal Code Country HomePhone Extension Photo Notes Reports To PhotoPath seTerritories ooo Territories oo- FK Products Categories FK Products Suppliers Employee Territories 8 Employee D 8 TerritoryID Territories Territory D TerritoryDescription RegionID FK Territories Region Region 8 RegionID RegionDescription -C Categories 8 CategoryID CategoryName Description Picture Suppliers 8 Supplier D CompanyName ContactName Contact Title Address City Region Postal Code Country Phone Fax HomePage

Answer rating: 100% (QA)

Answer creating a nonclustered index on the ShipVia column of the Orders table with included columns can affect the execution plan of the query you pr... View the full answer