Saturday, October 14, 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 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

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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog