Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Server Schema Binding and Indexed Views

MSSQLTips author Atif Shehzad By:   |   Read Comments (24)   |   Related Tips: More > Performance Tuning

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.  

So below shows what happened when I tried to create an index on the view I was using.

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 
salesorderidproductidunitpricelinetotalrowguid,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 
salesorderidproductidunitpricelinetotalrowguid,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.

In the following screenshot we are trying to create a non-clustered index on the view and we get the following error:

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.


Next Steps

  • If you are having poor performance when using views, look at creating indexes on the views

  • One of the criteria for indexed views is to use the schema binding option.  When objects are schema bound this also reduces the accidental dropping or altering of objects that are required in your database.



Last Update: 10/22/2008


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, November 04, 2008 - 6:24:46 AM - dpalepu54321 Read The Tip

Very informative and interesting article.

 


Wednesday, November 05, 2008 - 6:36:16 AM - TekKnight Read The Tip

Something to mention is that if you're NOT using SQL Server Enterprise edition, in order for your query to take advantage of the indexes you create, you need to specify WITH (NOEXPAND) after the FROM [schema.queryname] in the query calling the view.

Example:

SELECT SalesOrderID FROM dbo.vw_sampleView WITH (NOEXPAND)

If you are using the Enterprise edition this statement is not needed.


Wednesday, November 05, 2008 - 9:15:31 AM - TimothyAWiseman Read The Tip

 TekKnight has an excellent point.  

 Noexpand and its effects are looked at in greater detail at : http://www.sqlservercentral.com/articles/Indexed+Views/63963/ 

 Overall, this was a good article, if a bit basic.


Wednesday, November 05, 2008 - 12:16:49 PM - JeffJordan Read The Tip

The statement "You can not use aggregate functions when using schema binding." in the article is a bit misleading.  You can in fact use aggregate functions in indexed view and I have done so many times myself.  From BOL:

"An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated."

For a more more indepth infromation on Indexed view look at BOL.

 Hope this helps,

 Jeff


Wednesday, November 05, 2008 - 9:40:53 PM - @tif Read The Tip

 Jeff,

I have given this point in considerations and you pointed out right that it needs some more elaboration.

According to BOL 

The SELECT statement in the view cannot contain these Transact-SQL syntax elements:

The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions.

Complex aggregate function Substitute simple aggregate functions
AVG(X)SUM(X), COUNT_BIG(X)
STDEV(X)SUM(X), COUNT_BIG(X), SUM(X**2)
STDEVP(X)SUM(X), COUNT_BIG(X), SUM(X**2)
VAR(X)SUM(X), COUNT_BIG(X), SUM(X**2)
VARP(X)SUM(X), COUNT_BIG(X), SUM(X**2)

For example, an indexed view select list cannot contain the expression AVG(SomeColumn). If the view select list contains the expressions SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate the average for a query that references the view and specifies AVG(SomeColumn).

 

So i clearify that "you can not use AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions in select statement for a schema binded view ". 

 

jeff thanks for this point, i suggest that any onemay submit a tip on this topic seperately.


Wednesday, November 05, 2008 - 9:42:32 PM - @tif Read The Tip

 tek and timothy thanks for your addtional point.


Tuesday, November 11, 2008 - 3:11:57 AM - MAzim Read The Tip

Hi Atif,

Very pleased to see your good post. Keep up the good work.  

By the way in wich project you are currently working in PRAL

regards,

M Azim,

Sr DBA PRAL 

 


Thursday, June 04, 2009 - 1:52:25 PM - S B Read The Tip

 Hi Atif

The post is  very informative and elaborative.. But I have a problem. I need to index a view that has leftouter joins and  right outer joins, Is there a way to create an index on that view?.

Thanks in advance...


Thursday, June 04, 2009 - 5:33:00 PM - aprato Read The Tip

 Indexed views don't allow OUTER JOINs but there are workarounds. 

One "hack" (to me anyway) that I've seen is where you convert the view to use INNER JOINS but store a row in the table to indicate nullability so the query will still return a result set.  But before you turn to an indexed view, you may want to make sure you've made sure that the existing query plan is properly using the indexes on the base tables referenced in the existing view.  Indexed views cause overhead since they're updated as the base tables are updated.


Thursday, June 04, 2009 - 9:42:54 PM - @tif Read The Tip

I would refer a good solution of this particluar problem on following link

http://www.sqlservercentral.com/articles/Indexing/indexedviewswithouterjoins/1884/


Wednesday, September 15, 2010 - 1:34:13 PM - Mike Read The Tip
Since a schema bound view is physically similar to a table, I am wondering if there is a way to convert it to an actual, independent table?   I mean 'in-place', without creating a new table and then moving all the data from one to the other (which would require twice or three times the storage space and would be very very slow).


thanks,


Mike

 

(I posted the question @ sswug 1st because I didnt find your informative article until later)

 

 


Tuesday, March 06, 2012 - 5:44:37 AM - raja.dev Read The Tip

Good


Monday, March 19, 2012 - 2:31:47 PM - SQLDeveloper Read The Tip

I'm receiving the below error when trying to create a View WITH SchemaBinding by selecting from tables in different databases. It says we have to use two-part format but how can we reference a table in another database without database name. I appreciate any suggestions.

"Cannot schema bind view 'dbo.viewtmp' because name 'Database.dbo.TableA' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."


Monday, March 19, 2012 - 3:37:32 PM - Greg Robidoux Read The Tip

SQLDeveloper - I think the issue is that you cannot use schema binding across databases.


Monday, March 26, 2012 - 6:14:03 AM - Kuldeep Singh Read The Tip

Great Articles!! You elaborate it very nice and understandable. Here some good articles also helped me in completing my task. Check this helpful link too....
http://msdn.microsoft.com/en-us/library/ms187956.aspx
http://www.dbtalks.com/UploadFile/rohatash123/519/
http://www.codeproject.com/Articles/38560/Overview-of-View-in-SQL-Server-2005

 


Friday, April 13, 2012 - 1:18:47 PM - Abbas Read The Tip

Excellent article

 


Wednesday, July 11, 2012 - 9:41:18 PM - LR Bhat Read The Tip

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS for

 

Great. Went through many but found this is the very good posting.


Thursday, August 09, 2012 - 10:43:47 AM - Dave Johnson Read The Tip

Excellent article! Very well written and perfectly clear. Many thanks.


Wednesday, August 29, 2012 - 9:43:36 AM - Mohamed Irshad Read The Tip

Excellent Article. The schema binding is well explained. Thanks.

 


Monday, November 12, 2012 - 3:26:52 PM - shruthi Read The Tip

 

Neatly explained :) thanks

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS

and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.


Saturday, November 17, 2012 - 2:31:30 AM - BASKARAN Read The Tip

Wonderful...while i am creating view its really helpful for me..gud 

But while creating view with union of different table its have some constraints given how to overcome any idea..

 

Thanks in advance!


Tuesday, November 20, 2012 - 2:15:59 AM - Atif Read The Tip

@Baskaran, Along with many other operators UNION is also not allowed for indexed views. Instead of adding index to view, you may look for optimizing the base tables in this case.

Thanks

 


Monday, December 17, 2012 - 3:15:46 AM - Gil Shayer Read The Tip

Very good article !

 

It is important to note that it is not recommanded do bind a view to a table that might be altered by a program, using the SQL Server as a backbone layer. This will cause errors. If one decides to use it anyway -  good documentation is advised.

 

Thanks


Wednesday, April 17, 2013 - 3:12:47 PM - Don Read The Tip

Column name 'rowguid' does not exist in the target table or view.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.