Error:
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.
-- Table design
CREATE TABLE [dbo].[Sales_Summary_Test](
[SaleID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
[ProductLine] [nvarchar](2) NULL,
[SubTotal] [numeric](38, 6) NULL) ON [PRIMARY]
GO
-- Creating a View
CREATE VIEW dbo.vSumSales_Summary_TestByProductLine
WITH SCHEMABINDING
AS
SELECT s.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(s.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary_Test] AS s
WHERE ProductLine IS NOT NULL
GROUP BY s.ProductLine
go
SELECT * FROM vSumSales_Summary_TestByProductLine
go
-- Create a Index for view
CREATE UNIQUE CLUSTERED INDEX SumSales_Summary_TestIndex
ON dbo.vSumSales_Summary_TestByProductLine(ProductLine)
go
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.
Solution:
You must change NULL column to NOT NULL column
CREATE TABLE [dbo].[Sales_Summary_Test](
[SaleID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[ProductLine] [nvarchar](2) NOT NULL,
[SubTotal] [numeric](38, 6) NOT NULL) ON [PRIMARY]
GO