我有这样的看法：
create view dbo.VEmployeeSalesOrders
as
select
employees.employeeID, Products.productID,
Sum(Price * Quantity) as Total,
salesDate,
COUNT_BIG() as [RecordCount]
from
dbo.Employees
inner join
dbo.sales on employees.employeeID = sales.employeeID
inner join
dbo.products on sales.productID = products.ProductID
group by
Employees.employeeID, products.ProductID, salesDate
当我select * from dbo.VEmployeeSalesOrders需要执行计划的97％时。它需要更快。
当我尝试创建索引时，将引发以下消息，并引发异常：
选择列表未在count_Big（）上正确使用
为什么会出现此错误？
1首先，您需要更改视图并使其包含COUNT_BIG（）函数，因为您在选择语句中使用了聚合函数，并且使用的原因是SQL Server需要跟踪记录所在的记录，例如这个
create view dbo.VEmployeeSalesOrders
as
select employees.employeeID,Products.productID,Sum(Price*Quantity)
as Total,salesDate,COUNT_BIG(*) as [RecordCount]
from dbo.Employees
inner join dbo.sales on employees.employeeID=sales.employeeID
inner join dbo.products on sales.productID-products.ProductID
group by Employees.employeeID,products.ProductID,salesDate
2-然后您需要创建这样的索引
Create Unique Clustered Index Cidx_IndexName
on dbo.VEmployeeSalesOrders(employedID,ProductID,SalesDate)
希望能奏效
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句