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 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.
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.
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
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.
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.
*** 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.
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
Monday, March 19, 2012 - 3:37:32 PM - Greg Robidoux
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."
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)
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.
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
Complex aggregate function
Substitute simple aggregate functions
SUM(X), COUNT_BIG(X), SUM(X**2)
SUM(X), COUNT_BIG(X), SUM(X**2)
SUM(X), COUNT_BIG(X), SUM(X**2)
SUM(X), COUNT_BIG(X), SUM(X**2)
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
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 - 12:16:49 PM - JeffJordan
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 05, 2008 - 9:15:31 AM - TimothyAWiseman
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 04, 2008 - 6:24:46 AM - dpalepu54321