Trying to create a view with a cluster index, keep running into a brick wall with creating the view with schemabinding. I am using all 2 part identifiers in my query and I've followed all the rules on Microsoft's site (https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views)
For some reason it just doesn't want to enable schema binding (I was able to create the view without schemabinding, but I'm looking to have an indexed view for performance purposes)
alter view dbo.v_rolling_12_sales with SCHEMABINDING as select c.ChainName, c.ChainID, s.SupplierName, s.SupplierIdentifier, s.SupplierID, sum(isnull(i.TotalCost,0)) as costDollars, sum(isnull(i.TotalRetail,0)) as retailDollars, COUNT_BIG(*) as countBig from DataTrue_Report.dbo.InvoiceDetails as i with (nolock) inner join DataTrue_Report.dbo.Suppliers as s on s.SupplierID = i.SupplierID inner join DataTrue_Report.dbo.Chains as c on c.ChainID = i.ChainID where s.IsRegulated = 0 and i.SaleDate > '2/13/17' and i.InvoiceDetailTypeID in (1,7) and i.TotalQty > 0 group by c.ChainName, s.SupplierName, s.SupplierIdentifier, c.ChainName, c.ChainID, s.SupplierName, s.SupplierIdentifier, s.SupplierID GO CREATE UNIQUE CLUSTERED INDEX ChainSupplier ON v_rolling_12_sales (SupplierID, ChainID, ChainName, SupplierIdentifier, SupplierName) GO
Aggregate functions in a materialized view should not reference a null-able column. Try aggregate functions with non-nullable column. And also include count_big(*) along with your aggregate funtion.
Regards, Ananda Kumar J.