In this blog I am going to tell about dropping of indexes in 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
Now, I will create a view which will use this Customer table:
— Create view
Create VIEW vw_customer
SELECT CustomerID, CustomerName, CEO
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);
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.
Create unique clustered index IX_CustomerID
ON vw_customer (CustomerID)
Now our Indexed view is created having clustered index on it.
Now, I want to alter my view and add one more column GETDATE() as CurrentDate in view definition and alter the view.
Now alter the view after Scenario 2
ALTER VIEW vw_customer
SELECT CustomerID, CustomerName , GETDATE() as CurrentDate
Now, as I have altered the view, I want to create another index on column CustomerName which will be a NonClustered index.
Create index IX_CustomerName
ON vw_customer (CustomerName);
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.