Uncategorized

How index gets dropped in Views in SQL Server

Hi Friends,

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

(

[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 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:

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.

Error_Msg1

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 add one more column GETDATE() as CurrentDate  in 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.

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

Follow me on Twitter

Advertisements

2 thoughts on “How index gets dropped in Views in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s