Error Creating an Indexed View.

I have trouble creating an indexed view, sample scripts attached. Any help/workaround much appreciated. Thx.

Create Sample Table

Create Table dbo.Test_IndexTable

(NumericSchoolDBN int NOT NULL,

SchoolYear smallint NOT NULL,

TermId tinyint NOT NULL,

CourseCode Varchar(10) NOT NULL,

SectionID smallint NOT NULL,

PeriodID smallint NOT NULL,

CycleDay smallint NOT NULL,

PRIMARY KEY

(

NumericSchoolDBN, SchoolYear,TermId,CourseCode,SectionID,PeriodID, CycleDay

)

)

Populate Sample Table

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 0)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 1)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 2)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 3)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 4)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 0)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 1)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 2)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 3)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 4)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 0)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 1)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 2)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 3)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 4)

Create View

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON

GO

-- Drop View dbo.Test_IndexTable_InxVw

-- Create view.

CREATE VIEW dbo.Test_IndexTable_InxVw

WITH SCHEMABINDING

AS

select NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID,

sum(power(2, cycleday)) As AsIS_CycleDayBinaryString,

COUNT_BIG(*) As NumofRows

From dbo.Test_IndexTable

Group By NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID

GO

if ObjectProperty(object_id('Test_IndexTable_InxVw'),'IsIndexable') = 1

Print 'Test_IndexTable_InxVw Is Indexable'

Else

Print 'Test_IndexTable_InxVw Is NOT Indexable'

--Create index on the view.

CREATE UNIQUE CLUSTERED INDEX UCX_Test_IndexTable_InxVw ON dbo.Test_IndexTable_InxVw (NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID)

GO

Error…

Server: Msg 8662, Level 16, State 1, Line 1

An index cannot be created on the view 'Test_IndexTable_InxVw' because the view definition includes an unknown value (the sum of a nullable expression).

Is a potential option here to leave out the SUM column and create the indexed view without this quantity and iff this quantity is needed it can then be computed as needed?|||It beats the purpose i.e. the only reason I would like to create an indexed view it to be able to represent it in a binary/string value based on the sum function.|||

I am not a big fan of "encoding" values like this, not for the least reason that if you have duplicates it means you get spurious results, but you could do this like:

case cycleday
when 0 then 1
when 1 then 2
when 2 then 4
when 3 then 8
when 4 then 16
when 5 then 32 else -999 end

The else -999 just ensures Non-nullability, and a way out of whack answer to enact some error handling.

|||

Are you running this on SQL Server 2000? You have hit a limitation there in terms of expression evaluation. You can workaround by creating a computed column on the table like:

alter table dbo.Test_IndexTable add CycleDay_p as isnull(power(2, CycleDay), 0)

And using it in the indexed view definition like:

CREATE VIEW dbo.Test_IndexTable_InxVw

WITH SCHEMABINDING

AS

select NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID,

sum(CycleDay_p) As AsIS_CycleDayBinaryString,

COUNT_BIG(*) As NumofRows

From dbo.Test_IndexTable

Group By NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID

GO

Btw, you should be able to use the ISNULL expression directly in the indexed view in SQL Server 2005. I tried it there and it works fine.