Cannot schema bind view 'v_rolling_12_sales' because name 'DataTrue_Report.dbo.InvoiceDetails' is invalid for schema binding

tc_NYC Source

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
sqlsql-serversql-server-2008-r2

Answers

answered 1 week ago Ananda Kumar Jayaraman #1

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.

comments powered by Disqus