SQL Server : cannot create index on view because it references derived table.

Question

How can I add a clustered index to the following view?

CREATE VIEW [vExcludedIds] 
WITH SCHEMABINDING 
AS 
    SELECT DISTINCT
        TempTable.Id 
    FROM
        (VALUES (1), (2), (3)) AS TempTable(Id) 

And my index creation command is:

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
ON [vExcludedIds] (Id);

And I get the following error:

Cannot create index on view "Test.dbo.vExcludedIds" because it references derived table "TempTable" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

Also, when I try to add the index manually in SQL Server Management Studio, I get an error at the top of "New Index" window saying:

HasClusteredColumnStoreIndex: unknown property.

Any ideas please?

Answer 1

Please read https://msdn.microsoft.com/en-AU/library/ms191432.aspx

There are a lot of limitations for creating indexed views.

... The SELECT statement in the view definition must not contain the following Transact-SQL elements:

• DISTINCT
• Derived table

Consider creating a table or table function