USE AdventureWorksDW2017; /* 2. Consider the following 4-table-join query: SELECT GEO.EnglishCountryRegionName, CUST.FirstName, CUST.LastName, P.EnglishProductName FROM FactInternetSales AS FIS INNER JOIN DimProduct AS P ON P.ProductKey=FIS.ProductKey INNER JOIN DimCustomer AS CUST ON CUST.CustomerKey=FIS.CustomerKey INNER JOIN DimGeography AS GEO ON GEO.GeographyKey=CUST.GeographyKey ORDER BY GEO.EnglishCountryRegionName ASC, CUST.LastName ASC, CUST.FirstName ASC, P.EnglishProductName ASC;

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 create an index:

DROP INDEX IF EXISTS FactInternetSales.idxCustProd;

CREATE NONCLUSTERED INDEX idxCustProd ON [dbo].FactInternetSales (CustomerKey, ProductKey);

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 FactInternetSales table. */