We help IT Professionals succeed at work.

create indexed view error

abgconsulting
on
I have this error when creating indexed view:
error:
Msg 10109, Level 16, State 1, Line 1
Cannot create index on view "REFAC_DATA.dbo.VW_ScoreCard" because it references derived table "sal" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

here my view:
CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT
    y.c1, X.c2
FROM
    (SELECT
        1 AS c1) y
    INNER MERGE JOIN
    (SELECT
        1 AS c2) X ON y.c1 = x.c2;
GO

error executing:
CREATE UNIQUE CLUSTERED INDEX cix_v
    ON dbo.v (c1);
GO


Comment
Watch Question

CERTIFIED EXPERT

Commented:
What happens if you make views out of the two derived tables and then use them in the view "v" ?   (create with schemabinding)

Author

Commented:
Thanks for your reply.
This was just an example.
LEt's go with:

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT
    y.c1, X.c2
FROM
    (SELECT
        c1 from table1) y
    INNER MERGE JOIN
    (SELECT
        c2 from table2) X ON y.c1 = x.c2;
GO

error executing:
CREATE UNIQUE CLUSTERED INDEX cix_v
    ON dbo.v (c1);
GO


Do you explain more  what you mean?

Thanks,

Alioune.
CERTIFIED EXPERT

Commented:
Let's table my first proposal for the moment ... why don't you just use a straight join in the view "v", instead of the sub-queries?

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT  y.c1, X.c2
FROM table1 y
INNER MERGE JOIN table2 X
ON y.c1 = X.c2;
GO
CERTIFIED EXPERT

Commented:
-- my original idea (which I don't know if this will work) is to create views out of the sub-queries:

CREATE VIEW dbo.v1
WITH SCHEMABINDING
AS
SELECT 1 AS c1
GO

CREATE VIEW dbo.v2
WITH SCHEMABINDING
AS
SELECT 1 AS c1
GO

-- then use these views in "v"

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT y.c1, X.c2
FROM dbo.v1 y
INNER MERGE JOIN dbo.v2 X
ON y.c1 = X.c2;
GO

CERTIFIED EXPERT

Commented:
oops, the second view of the three above should alias the value as c2 instead of c1

Author

Commented:
Ok,

but this was just a simplified query. My query is really more complex. My question is: it is possible to create indexed view using derived tables?
IS there a workaround about this error?

Thanks.
Commented:
No there are certain constraints and using a dervied data in select statement is one of them. You can find the details here:-
http://msdn.microsoft.com/en-us/library/ms191432.aspx

The only solution is to use joins and then create the view.