Sql-server – Unique clustered index on indexed view fails because of aggregates

indexmaterialized-viewsql server

I want to create an indexed view similar to this (slightly simplified):

Create VIEW dbo._v1
WITH SCHEMABINDING
AS 
    SELECT 
         COUNT_BIG(*) as CB
        ,[ID]
        ,TITLE] 
        ,dbo.getXmlTranslation1(acat.[TITLE], 'de-DE') as T 
    FROM dbo.[ArticleCategory] acat 
    Group by 
         [ID]
        ,[TITLE]

Creation of a unique clustered index on this fails:

CREATE unique CLUSTERED INDEX __CLIX
ON dbo._v1([ID]);

Cannot create the clustered index '__CLIX' on view 'db._v1T' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

I searched for a solution but can't find one. If I remove the GROUP BY (I don't really need here), I am told that the Unique Clustered index can't be created because of multiple [ID] results.
I added the scalar function call in order to workaround the XML operation within the indexed view that is not allowed as well.
I checked if the scalar funtion is deterministic, what is the case.

Best Answer

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.

Related Question