When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the definition of objects that are referenced by schema bound.
WITH SCHEMABINDING can be used in Views and T-SQL Functions, but not in Stored Procedures.
SCHEMABINDING in view:
CREATE VIEW [dbo].[VIEW_NAME] WITH SCHEMABINDING AS ....
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. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. 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. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.
SCHEMABINDING in functions:
CREATE FUNCTION [FUNCTION_NAME] (@INPUT ...) RETURNS ... WITH SCHEMABINDING
Specifies that the function is bound to the database objects that it references. When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition. The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.
Improving query plans with the SCHEMABINDING option on T-SQL UDFs
No comments:
Post a Comment