none
CLR and Indexed view RRS feed

  • Question

  • I am trying to create a non-clustered index an a CLR-derived column in an indexed view, but it doesn't allow me. Here is the error message:
    Cannot create index or statistics 'Ix_IndexedView_One' on view 'vwIndexedViewTest' because cannot verify key column 'idPlusOne' is precise and deterministic. Consider removing column from index or statistics key, marking column persisted in base table if it is computed, or using non-CLR-derived column in key.

    Books online says that CLR derived columns can't be part of the clustered index, but doesn't mention non-clustered indexes.

    Here is the test script:

     

    The table:

    create table [dbo].[IndexedViewTest](
     [id] [int] identity(1,1) not null,
     [name] [varchar](50) null
     constraint PK_id primary key clustered 
     (
     [id] asc
     )
    )
    


    CLR function:

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,IsPrecise=true, DataAccess=DataAccessKind.None, SystemDataAccess=SystemDataAccessKind.None)]
    public static SqlInt32 AddOne(SqlInt32 number)
    {
      return number + 1;
    }
    

    Indexed view:

    create view dbo.vwIndexedViewTest
    with schemabinding
    as
    select id, name, dbo.AddOne(1) as idPlusOne
    from dbo.IndexedViewTest
    go
    create unique clustered index Ix_IndexedView on vwIndexedViewTest(id)

    Now trying to create an index on the CLR column:

    create index Ix_IndexedView_One on vwIndexedViewTest(idPlusOne) --getting an error
    

    You can definitely create indexes on calculated CLR columns as long as they are persisted. As you can see, all settings for the CLR are correct, I don't see why SQL Server wouldn't be able to create an index on that column..

    Friday, June 3, 2011 1:52 PM

Answers

  • Hi,

    This derived column of view does not exist in the base table, you may need to put it in the base table as a computer column and mark it as PERSISTED.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, June 9, 2011 5:39 AM
    Moderator

All replies

  • Hi,

    This derived column of view does not exist in the base table, you may need to put it in the base table as a computer column and mark it as PERSISTED.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, June 9, 2011 5:39 AM
    Moderator
  • Would it be better to set this up as a database user-defined function instead? Then you should be aware of the following:

    Might make your life easier:

    CREATE FUNCTION AddOne(@param int) RETURNS int WITH SCHEMABINDING

    AS

    BEGIN

    SET @param = @param+1

    RETURN @param

    END

    It needs to be a schemabinding function to be referenced in an indexed view. Hope this helps, sorry if it doesn't.

    D.

    Thursday, June 16, 2011 4:39 PM
  • I have the same problem.  I cannot do this because a computed column can only depend on one table.  The number of limitations related to materialized/indexed views is utterly infuriating.
    Tuesday, August 20, 2019 4:03 PM