I am trying to create an indexed view in SQL Server 2012 on a query that has 10 joins (inner and left), which is accessed a lot.
However, when attempting to create the view, I get an error:
Cannot schema bind view 'vw_transaction' because name 'entity_event' is invalid for schema binding.
I am (attempting to) create the view using:
CREATE VIEW vw_transaction WITH SCHEMABINDING AS Select ee.id as entity_event_id, ....
Is there a reason for this error? It looks like it's a reserved word, as the error mentions a 'name', as opposed to a column. Entity_event
is the name of my main table.
Schema bound views must reference base tables only. They cannot reference other views. Schema bound views must be in the same database and schema as the referenced base tables. Multiple base tables utilize a two-part naming convention for specifying the columns for the view definition.
There are two ways to remove schema binding from a view: Alter the view so that its definition no longer specifies schema binding. Drop the view (then re-create it without schema binding if required).
SCHEMABINDING. Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
I fixed the issue by adding the schema name in front of all table references:
select [..] from schema_name.table_name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With