Drop Index on View in SQL Server

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:

CREATE TABLE [dbo].[Customer]

(

[CustomerID] [int] IDENTITY(1,1) NOT NULL,

[CustomerName] [varchar](50) NOT NULL,

[CEO] [varchar](40) NULL,

[Phone] [varchar](20) NOT NULL

PRIMARY KEY CLUSTERED

(

[CustomerID] ASC

)

)

GO

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

Create VIEW vw_customer

 WITH SCHEMABINDING

AS

 SELECT  CustomerID, CustomerName, CEO

  from dbo.Customer

  GO

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:

Create index IX_CustomerID

 ON vw_customer (CustomerID);

 GO

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.

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

Scenario 2

Create unique clustered index IX_CustomerID

 ON vw_customer (CustomerID)

 GO

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

Alter  VIEW vw_customer

 WITH SCHEMABINDING

AS

 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate

  from dbo.Customer

GO

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

Scenario 4

Create index IX_CustomerName

 ON vw_customer (CustomerName);

GO

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

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