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
Question:
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.