January 16, 2015 at 10:56 pm
I was reading this blog below about Indexed Views and needed some clarification.
When the author choose to create the unique clustered index he excluded the PER.BusinessEntityID
columns and only included, (CustomerID, SalesOrderID, ProductID).
Why didn't the PER.BusinessEntityID get added?
https://www.simple-talk.com/sql/learn-sql-server/sql-server-indexed-views-the-basics/
CREATE VIEW Sales.vSalesSummaryCustomerProduct
WITH SCHEMABINDING
AS
SELECT CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID ,
SUM(SOD.OrderQty) AS TotalOrderQty ,
SUM(LineTotal) AS TotalValue ,
COUNT_BIG(*) AS CountLines
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesOrderDetail SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product PROD
ON PROD.ProductID = SOD.ProductID
INNER JOIN Sales.Customer CUST
ON SOH.CustomerID = CUST.CustomerID
INNER JOIN Person.Person PER
ON PER.BusinessEntityID = CUST.PersonID
GROUP BY CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX CX_vSalesSummaryCustomerProduct
ON Sales.vSalesSummaryCustomerProduct(CustomerID, SalesOrderID, ProductID);
GO
January 17, 2015 at 1:48 am
smitty-1088185 (1/16/2015)
I was reading this blog below about Indexed Views and needed some clarification.When the author choose to create the unique clustered index he excluded the PER.BusinessEntityID
columns and only included, (CustomerID, SalesOrderID, ProductID).
Why didn't the PER.BusinessEntityID get added?
https://www.simple-talk.com/sql/learn-sql-server/sql-server-indexed-views-the-basics/
CREATE VIEW Sales.vSalesSummaryCustomerProduct
WITH SCHEMABINDING
AS
SELECT CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID ,
SUM(SOD.OrderQty) AS TotalOrderQty ,
SUM(LineTotal) AS TotalValue ,
COUNT_BIG(*) AS CountLines
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesOrderDetail SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product PROD
ON PROD.ProductID = SOD.ProductID
INNER JOIN Sales.Customer CUST
ON SOH.CustomerID = CUST.CustomerID
INNER JOIN Person.Person PER
ON PER.BusinessEntityID = CUST.PersonID
GROUP BY CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX CX_vSalesSummaryCustomerProduct
ON Sales.vSalesSummaryCustomerProduct(CustomerID, SalesOrderID, ProductID);
GO
Quick thought, looks like the scope/focus is on the Customer (as in business) not the Employee of the Customer.
😎
January 18, 2015 at 9:09 am
The grouping for the view is done on those three columns so that is where you want the index on as that is what any query on the view would have to be grouped by.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply