SQL Server Schema Binding and Indexed Views

By:   |   Comments (31)   |   Related: > Performance Tuning


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".


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
SELECT INTO SampleTable 
FROM sales.SalesOrderDetail

--If the view already existed we could use this to add SCHEMABINDING

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

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 21, 2017 - 6:23:49 AM - Duncan Back To Top (74302)

I just ran into this issue and this article was exactly what I needed to fix the issue. 


Many thanks!


Wednesday, September 14, 2016 - 3:14:41 PM - Srikanth Back To Top (43319)



i created the following view

Create view [dbo].[VwCtyHstPrtCtyHtsDtl] with schemabinding as

select ROW_NUMBER() OVER (PARTITION BY P.prtid ORDER BY prtcd DESC) AS rown,Companycd,prtcd,P.descr,ctycd,hts,H.modifiedby,H.modifieddate,H.Dilogid,P.Prtid,P.isactive 

from dbo.ctymstprt P inner join dbo.ctyhstprtctyhts H on H.Prtid=P.Prtid where isactive=1900

group by Companycd,prtcd,ctycd,hts,h.modifiedby,h.modifieddate,h.Dilogid,p.Prtid,P.isactive ,descr



but i was unable to create index on this view.



Tuesday, February 16, 2016 - 4:26:03 AM - Sadiq Back To Top (40691)

 Thanks Atif,

For sharing your experience and knowldege on Schema Binding, I should have seen this 1 day before so that I could have answered in Interview ;)





Thursday, August 6, 2015 - 7:31:55 AM - Laura Saad Pelegrina Back To Top (38392)

I got it, thank you. Yesterday I did a test with tables in the same database and it worked fine. I will keep this page on my favorite links.


Thank you.





Thursday, August 6, 2015 - 2:19:36 AM - Atif Shehzad Back To Top (38391)

As mentioned in error statement names must be in two part format i.e. schemaName. onjectName. So it is limitation that you cannot access cross database objects for indexed views.

Wednesday, August 5, 2015 - 11:45:55 AM - Laura Saad Pelegrina Back To Top (38382)

Msg 4512, Level 16, State 3, Procedure MovingAvgCost, Line 5


Cannot schema bind view 'dbo.MovingAvgCost' because name 'CDF_DataWhs.dbo.Billings' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


Wednesday, August 5, 2015 - 9:41:19 AM - Laura Saad Pelegrina Back To Top (38378)

Hello, Please Atif, I really liked your post.

I´m folowing your steps to make my query faster. But I got one of the error that you wrote and I don´t know how to fix. My view and my source tables are in different DBs.


Thank you very much.







view [dbo].[MovingAvgCost] withschemabinding






























ISNULL(msv.baseuom,isnull(msv2.baseuom,'STD'))AS MSV_UOM,


ISNULL(msv.MovingAvgPrice,isnull(msv2.MovingAvgPrice,0))AS MovingAvgPrice ,--if ZER0 apply STD COST into MSTR


ISNULL(msv.PriceUnit,isnull(msv2.PriceUnit,0))AS PriceUnit --if ZER0 apply STD COST into MSTR


from CDF_DataWhs.dbo.Billings b


LEFTjoin CDF_DataWhs.dbo.MaterialStockValuation msv


on msv.materialnum = b.materialnum


and msv.plant = b.plant


and msv.period = b.BillingPeriod


LEFTjoin CDF_DataWhs.dbo.MaterialStockValuation msv2


on msv2.materialnum = b.materialnum


and msv2.plant = b.plant


and msv2.period =(selectmax(Period)from CDF_DataWhs.dbo.MaterialStockValuation msv3


where msv3.materialnum = b.materialnum


and msv3.plant = b.plant)







Wednesday, April 17, 2013 - 3:12:47 PM - Don Back To Top (23404)

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

Monday, December 17, 2012 - 3:15:46 AM - Gil Shayer Back To Top (20977)

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.



Tuesday, November 20, 2012 - 2:15:59 AM - Atif Back To Top (20424)

@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.



Saturday, November 17, 2012 - 2:31:30 AM - BASKARAN Back To Top (20390)

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!

Monday, November 12, 2012 - 3:26:52 PM - shruthi Back To Top (20297)


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.

Wednesday, August 29, 2012 - 9:43:36 AM - Mohamed Irshad Back To Top (19277)

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


Thursday, August 9, 2012 - 10:43:47 AM - Dave Johnson Back To Top (18980)

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

Wednesday, July 11, 2012 - 9:41:18 PM - LR Bhat Back To Top (18455)

*** 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.

Friday, April 13, 2012 - 1:18:47 PM - Abbas Back To Top (16904)

Excellent article


Monday, March 26, 2012 - 6:14:03 AM - Kuldeep Singh Back To Top (16608)

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


Monday, March 19, 2012 - 3:37:32 PM - Greg Robidoux Back To Top (16513)

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

Monday, March 19, 2012 - 2:31:47 PM - SQLDeveloper Back To Top (16512)

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."

Tuesday, March 6, 2012 - 5:44:37 AM - raja.dev Back To Top (16259)


Wednesday, September 15, 2010 - 1:34:13 PM - Mike Back To Top (10160)
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).




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



Thursday, June 4, 2009 - 9:42:54 PM - @tif Back To Top (3508)

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


Thursday, June 4, 2009 - 5:33:00 PM - aprato Back To Top (3506)

 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 4, 2009 - 1:52:25 PM - S B Back To Top (3505)

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

Tuesday, November 11, 2008 - 3:11:57 AM - MAzim Back To Top (2190)

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


M Azim,



Wednesday, November 5, 2008 - 9:42:32 PM - @tif Back To Top (2153)

 tek and timothy thanks for your addtional point.

Wednesday, November 5, 2008 - 9:40:53 PM - @tif Back To Top (2152)


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

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 5, 2008 - 12:16:49 PM - JeffJordan Back To Top (2150)

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,


Wednesday, November 5, 2008 - 9:15:31 AM - TimothyAWiseman Back To Top (2148)

 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 5, 2008 - 6:36:16 AM - TekKnight Back To Top (2147)

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.


SELECT SalesOrderID FROM dbo.vw_sampleView WITH (NOEXPAND)

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

Tuesday, November 4, 2008 - 6:24:46 AM - dpalepu54321 Back To Top (2136)

Very informative and interesting article.


get free sql tips
agree to terms