Hi Friends,

This is my first blog for SqlServerGeeks. In this blog I am going to tell about drop index on View in Sql Server.

Views are virtual tables in which data comprised from one or more tables and gives the result set as our SQL table does with rows and columns. A view doesn’t store the data permanently in the database and at the time of execution only its result set get determined.

When a view contains large amount of rows, complex logic in it then we can create an index on a view to improve the query performance. A view consists of a Unique Clustered Index and it is stored in database as clustered index does.

Now, let’s run few scenarios to check when Clustered index which is created on a view gets dropped automatically.

First I will create a table on which I will run those scenarios:

Now, I will create a view which will use this Customer table:

Here in definition of view I have used WITH SCHEMABINDING which is necessary for creating index on a view. This option simulates that we cannot delete any of the base table used in view, in order to make any changes first we need to drop or alter the view.

Also, all the tables references in a view should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our view:

Error returned: Cannot create index on view ‘vw_customer’. It does not have a unique clustered index.

On views, the first index must be a unique clustered index, so this will throw the error.

Error_Msg1

So, first index on view should be UNIQUE CLUSTERD INDEX else it will throw an error.

Scenario 2

Now our Indexed view is created having clustered index on it.

Now, I want to alter my view and added one more column GETDATE() as CurrentDate  in view definition.

Now alter the view after Scenario 2

Scenario 3

Now, as I have altered the view, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4

Again, I get an error: Cannot create index on view ‘vw_customer’. It does not have a unique clustered index. Error_Msg1

As we have already created Unique Clustered Index on view still its gives an error.

The interesting thing is that after updating the view, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned in bol here:

“ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.”

Hope you will like this post.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter