August 2, 2013 at 3:18 am
I have come across a meaning of schema binding in views which i am unable to understand
the following is the explanation:
" Binds the view to the schema of the underlying table or tables."
my question is
1) what is this underlying table?
"When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition."
my question is
2) Which base table the explanation is talking about that cannot be modified?
" The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified."
my question is
3) can you explain the above statement?
When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
"Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error."
my question is
4) is this right that we cannot drop table that has been used in views with schema binding?
Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.
"
my question is
5) i have tried altering the view with schema binding it altered successfully but the explanation says otherwise?
August 2, 2013 at 4:21 am
If you create a view of a table with schemabinding, you cannot drop columns referenced in the view from the table without first changing the view. If you query sys.sql_dependencies, you can see that each column in the view definition has a dependency on its column in the base table. If you created the view without schemabinding, you could drop a column from the base table without error, but your view (if it references the dropped column) would not work any more.
August 2, 2013 at 3:40 pm
1. The tables referred to into the view, directly or indirectly through other views.
2. The schema of the table cannot be modified freely. You cannnot drop or alter columns referred to by the view. You can still modify data in the table.
3. The point with SCHEMABINDING is that this gives a gurantee that the view remains valid and with the same definition as it had originally.
4. Yes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply