Aim: – Is it possible to create indexes on views without having schema binding option?
This article will present you the knowledge about the topics like Indexes, Views and Schema binding. We are aware with the fact that creating indexes on the views will help to enhance the performance. But is it so easy to perform this task in reality or there is some criteria which needs to be followed before you create indexes on the views. All these questions were taken into consideration while this post was written. Let’s explore these questions practically and find out the answers for these.
Description: – The answer for this question is, NO. We cannot create indexes on views without using schema binding option. If we try to do so i.e. creating indexes on views than it will throw an error message “Cannot create index on view ‘*’ because the view is not schema bound (Microsoft SQL Server, Error: 1939)”. Let us see why this error occur and how can we resolve this issue. Let’s quickly open SSMS (SQL Server Management Studio) and create a database.
–Create Database PHPRING
create database phpring
–Create a Table emp
create table emp (
eid int identity(1,1), ename varchar(10) )
After creating a table with the name emp, let’s feed this table with some values.
–Insert few rows into Table EMP
insert into emp values(‘chander sharma’),(‘avinash reddy’),(‘sai krishna’),
(‘pinal dave ‘),(‘vinod kumar’),(‘balmukund’)
–Read the Table emp
select * from emp
As we can see in the output pane, values are inserted correctly into the table. Now, we will create a view on emp table.
–Creating View on emp Table
create view v_avinash AS select eid,ename from emp
NOTE: – How to see whether our view exist or not on emp table?
We can know the status of this by using SYS.VIEWS table. Before calling that table, ensure that we are in PHPRING database because our emp table exists in that database only.
–Following screen shot will show the View (v_avinash) exists or not on emp table
select * from sys.views
–Creating clustered Index on View v_avivash
create clustered index emp_data on v_avinash(eid)
On executing the above command we got an error message. The above screen shot clearly suggests that in order to create an index on a view we have to make it as schema bound.
I know many questions will be coming to your brain like – what is SCHEMA BOUND, what is the purpose of it and why we can’t create index on view without using schema binding option?
In SQL Server, views are not bound to the schema of the base tables by default. Schema binding ties an object to the base object it depends upon. So, without schema binding if a view is created and the underlying table is changed than the view may break, but the table change can still occur. So, 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.
Points to Consider :-
- You cannot create an index on a view with outer joins used in it, even if you use schema binding.
- You cannot use ‘*’ in the select statement of a view when it is schema bound.
- You cannot use aggregate functions when using schema binding.
- When objects are schema bound this also reduces the accidental dropping or altering of objects that are required in your database.
In this article, we create a database and inserted some values into the table. A view was created for the table and we tried to create indexes on views but got an error message which tells us that to create indexes on views we have to use the “WITH SCHEMA BINDING” clause.
- How to create a view.
- Creating clustered index on views.
- What is Schema bound.
Hope you all enjoy this article and it helps in validating your concepts about Indexes, Views and Schema binding in SQL Server. Your comments and queries are highly appreciated.