1

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.

  • I assume the function is your issue here (but just a guess). What is that function doing? Maybe even put the code of the function here as well, as that might help. – mskinner Sep 3 '15 at 13:21
  • You're going to have to materialize the results of the function elsewhere, I suspect. – Aaron Bertrand Sep 3 '15 at 13:31
7

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.

  • TY for this Suggestion. This is a good idea. The reason why i added the func was that there is a XML col I want to "read". Because I got an error because of using XML in the viel I followed this article in order to workaround this... 31a2ba2a-b718-11dc-8314-0800200c9a66.com/2010/06/… - in the article is Shown that functions, if deterministic, work in Indexed views. Don't they in General? – Magier Sep 3 '15 at 21:12
  • 1
    In general, deterministic functions will work. But that was why I asked for the code of your function, as I am betting it is not. That said, here is another good SE post that describes a situation similar to yours and what they did to work around it (similar to what I said). stackoverflow.com/questions/6617972/… – mskinner Sep 3 '15 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.