Can we create indexes on views without having schema binding?

6
2562

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.

Can we create indexes on views without having schema binding

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 PhpRinG Database

–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

Read emp Table

 

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

Show view exists or not

 

–Creating clustered Index on View v_avivash

 create clustered index emp_data on v_avinash(eid)

Schema Bound error

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.

Summary:-

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.

Lessons learn:-

  • How to create a view.
  • Creating clustered index on views.
  • What is Schema bound.

References:-

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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 10.0/10 (19 votes cast)
VN:F [1.9.22_1171]
Rating: +20 (from 20 votes)
Can we create indexes on views without having schema binding? , 10.0 out of 10 based on 19 ratings
  • Beena

    Thanks Avinash,

    Great post. I look forward to use this in future, if I get into database role.

  • senzosh

    Nice article…
    Can you explain the reason once again why we can’t create index without schema binding… explaination part only….

    • well first of all thank you so much for visiting this site and great thanks for your support.

      coming to your doubt ,
      what is view ?
      a view is like a stored query ,it doesnt store any data just it stores the structure what ever we mentioned at the time of view creation.so if we start to call a view how the view shows the data ,it doesn’t store right the reason is it internally calls the underlaying table & hit that table to take the data and it shows that in result set.

      so if somebody is accidentally or intensionally deleted the original table at that time can we retrieve the data from view ? no ,we cant retrieve why because the base table is deleted by someone.

      ofcourse,we can create index on table or a view but before doing this we must make it as schema bound ,i hope you knew about schema bound

      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.

  • sanjiv

    helpfull…