Saturday, 14 October 2017

How to Create a View with Schema Binding in SQL Server

Creating a View with SchemaBinding in SQL Server
The SHEMABINDING is essentially takes the things that your VIEW is depend upon(Tables/other Views), and "Binds" them to that View. The significance of this is that, no one can make alteration(ALTER/DROP) to those underlying objects, unless you drop the Schema-Bound View first.

It prevents your VIEW from becoming impact by alterations made to the underlying objects.
If you want to create an Index on your View, you must create it with using the " WITH SCHEMEBINDING" Option.
If you want to create a Schema-Bound user defined Function that references to your View then your View must also be " SCHEMEBINDING".
Example :
Creating a View with SchemaBinding Option


CREATE VIEW vw_EmpDeptDtls



  FROM [dbo].[Emp_Test] E Inner Join [dbo].[Dept] D
  ON E.Dept_Id=D.Dept_Id
    Inner Join [dbo].[JobTitles] J
  ON E.Job_Id=J.Job_Id

Now you can create an Index on the above View to make the Query execution faster :

Create Unique Clustered Index Ind_Vw_EmpDept
On vw_EmpDeptDtls (Emp_Id,Dept_Id,Job_Id)


Notes :
Now if you try to make any alterations to the underlying objects...
--ALTER TABLE EMP_TEST Drop Column [Job_Id]
--ALTER TABLE EMP_TEST ALTER Column [Emp_Name] Varchar(250)
--Drop Table [Emp_Test]

You will get the following errors...
--The object 'vw_EmpDeptDtls' is dependent on column 'Job_Id'
--ALTER TABLE DROP COLUMN Job_Id failed because one or more objects access this column.
--Cannot DROP TABLE 'Emp_Test' because it is being referenced by object 'vw_EmpDeptDtls'

Please note that :
--You can Alter the underlying objects of the View, by removing the SchemaBinding by altering the View .
--You can drop/alter a Column from the base table, which is not used in the View.

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts