Schema Binding and Indexed Views in SQL Server
Problem
Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound".
Solution
The error message "Cannot create index on view '*' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.
In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.
So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.
To use schema binding for views, first we will create a sample table in AdventureWorks database then a view "vw_sampleView" is created on this new table.
Create sample table and sample view
USE AdventureWorks
GO
SELECT * INTO SampleTable
FROM sales.SalesOrderDetail
GO
CREATE VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS
SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate
FROM dbo.SAMPLETABLE
GO
--If the view already existed we could use this to add SCHEMABINDING
ALTER VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS
SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate
FROM dbo.SAMPLETABLE
GO
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
So here we are with a SCHEMABOUND view "sampleView" on base table "sampleTable". Now we will check that we are able to create an index on the view.
An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView]
(
[rowguid] ASC
)
GO
When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding
You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".
You will not be able to create clustered index on a view if the view references any nondeterministic functions.
You can not use aggregate functions when using schema binding.
You can not migrate the base table of a schema bound view.
--------------------------------------------------------------------------------
Now we are left with our second part of the problem, how schema preservation is implemented for the base table. Again we have our "sampleTable" and "vw_sampleView" with schema binding. We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.
By using SSMS to alter the data type of a column in the base table"sampleTable", I get a warning message notifying me that because of the schema bound view this will not work.
To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.
On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.
Ref :- http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/
Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound".
Solution
The error message "Cannot create index on view '*' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.
In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.
So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.
To use schema binding for views, first we will create a sample table in AdventureWorks database then a view "vw_sampleView" is created on this new table.
Create sample table and sample view
USE AdventureWorks
GO
SELECT * INTO SampleTable
FROM sales.SalesOrderDetail
GO
CREATE VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS
SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate
FROM dbo.SAMPLETABLE
GO
--If the view already existed we could use this to add SCHEMABINDING
ALTER VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS
SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate
FROM dbo.SAMPLETABLE
GO
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
So here we are with a SCHEMABOUND view "sampleView" on base table "sampleTable". Now we will check that we are able to create an index on the view.
An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView]
(
[rowguid] ASC
)
GO
When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding
You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".
You will not be able to create clustered index on a view if the view references any nondeterministic functions.
You can not use aggregate functions when using schema binding.
You can not migrate the base table of a schema bound view.
--------------------------------------------------------------------------------
Now we are left with our second part of the problem, how schema preservation is implemented for the base table. Again we have our "sampleTable" and "vw_sampleView" with schema binding. We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.
By using SSMS to alter the data type of a column in the base table"sampleTable", I get a warning message notifying me that because of the schema bound view this will not work.
To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.
On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.
Ref :- http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/
Comments
Post a Comment