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.

Notes:
It prevents your VIEW from becoming impact by alterations made to the underlying objects.
If you want to create an Index on you 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

USE [TAMATAM]
GO


CREATE VIEW vw_EmpDeptDtls

WITH SCHEMABINDING

AS

SELECT
 E.[Emp_Id],
  E.[Emp_Name],
  E.[Gender], 
  E.[Joining_Date],
  E.[Basic_Sal],
  D.[Dept_Id],
  D.[Dept_Name],
  D.[Bonus_Rate],
  J.[Job_Id],
  J.[Job_Title]
  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

GO
--------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts