Drop Indexes In Views In SQL Server

In this article, I am going to tell about the dropping of indexes in View in SQL Server.

Views are virtual tables in which data from one or more tables 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 gets determined.

When a View contains a large amount of rows and has 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 the database as the clustered index does.

Now, let’s run a 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
Create VIEW vw_customer
WITH SCHEMABINDING
AS
SELECT CustomerID, CustomerName, CEO
from dbo.Customer
GO

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

Also, all the table 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.

Scenario 1

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, the 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 a clustered index on it. Now, I want to alter my View and add one more column GETDATE() as CurrentDate in the View definition and alter the View.

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 it 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 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.