I think there's some misunderstanding about what you're attempting to do here.
Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something).
In order to fully aggregate the Value
column, all the supplementary columns cannot be included in the SELECT
list. Alternatively, if those columns are included in the GROUP BY
clause, the view would represent only a partial aggregation, as there would be one row for each unique combination of the columns in the GROUP BY
column list.
The only way I see something like this being useful is if the supplementary columns aren't included in the view, and there is some other process that requires only the daily aggregated values, without the base row data. Such a view could be defined like this:
CREATE TABLE [dbo].[BaseTbl]
(
ColRowID bigint NOT NULL,
AggregateID int NOT NULL,
Epoch int NOT NULL,
CustomerID int NOT NULL,
TypeID tinyint NOT NULL,
ErrorID smallint NOT NULL,
Value int NOT NULL,
PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO
CREATE VIEW [dbo].[ixvw_AggTbl]
WITH SCHEMABINDING
AS
SELECT
t.Epoch / 86400 AS EpochDay,
CustomerID,
TypeID,
SUM(t.Value) AS TotalValue,
COUNT_BIG(*) AS __RowCount
FROM [dbo].[BaseTbl] t
GROUP BY
t.Epoch / 86400,
CustomerID,
TypeID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);
Unfortunately, you can't go farther and convert the EpochDay
column to an actual date within the indexed view because DATEADD
is non-deterministic (see Aaron's comment below for why), so you'd have to convert it in the actual SELECT
query against the view. But that's not too difficult.
In any event, as I said before, I'm not sure how useful this would be for your specific application.
Your issue is definitely your function, and you will have to do something to make that work differently. Here is a very basic example to illustrate the problem.
First create your test data.
CREATE TABLE dbo.testResults
(
id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,col1 VARCHAR(200)
);
GO
INSERT INTO dbo.testResults
( col1 )
VALUES ( 'test1' ), ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test4' );
GO
Now we will create a view (that works correctly) and add the clustered index to that view.
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1;
GO
--Works great
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1);
GO
Next we will add a very simple and basic function that does nothing more than very basic string manipulation (left 3 characters of a given string).
CREATE FUNCTION dbo.left3 (@str varchar(200))
RETURNS varchar(3)
WITH schemabinding
AS
begin
RETURN LEFT(@str, 3)
END;
Now we will get rid of our previous view add this column to the view.
DROP VIEW dbo.vwResults;
GO
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,dbo.left3(col1) AS left3col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1, dbo.left3(col1);
GO
So far, so good. But then by adding even this simplistic function to the view and nothing else, we are no longer able to index the view.
/*FAILURE!!!!!!*/
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1);
GO
So for this example there is a pretty simple solution where I can fairly easily create a computed column and then everything works. Here is how I would do that.
ALTER TABLE dbo.testResults
ADD left3col1 AS LEFT(col1, 3);
After doing this, I can create the same view and this time I can add the unique clustered index as follows.
DROP VIEW dbo.vwResults;
GO
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,left3col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1, left3col1;
GO
/*IT WORKS!!!!!!*/
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1, left3col1);
GO
This might not be an option for your scenario, but basically your function is the problem and you need to work through some alternatives (depending on what it does) if you want to make an indexed view work for your scenario.
Best Answer
The base table insert plan incorporates operations necessary to keep the indexed view synchronized with the base table as defined by the view definition. This part of the plan is automatically generated and cannot be disabled, or made to call the instead of trigger logic on the view instead. The contract of an indexed view is that it will always materialize the query stored in the view.
The main purpose of instead of triggers on views (indexed or otherwise) is to make them updatable when they wouldn't be otherwise. The question doesn't explain what you need to achieve, but it seems an indexed view is not the right mechanism. You might need a separate table instead of a view. Please ask a follow-up question if you want help solving the underlying problem, and ideally provide a self-contained reproduction script.