Indexes on Views

27 Mar 2013

I tried to create an Index on View. Then SQL Server said I have to alter that index as a schema-bound view. Then I created a Schema Bound View.

USE AdventureWorks2012GO

CREATE VIEW [dbo].[vHR] WITH SCHEMABINDING 
AS 
    SELECT 
        BusinessEntityID AS A,
        NationalIDNumber AS B,
        LoginID AS C
    FROM HumanResources.EmployeeGO

Then I created Index on that View.

CREATE UNIQUE CLUSTERED INDEX UCI_vHR ON vHR(A,B)GO

Then after that, I altered index. And my index got disappeared suddenly.

ALTER VIEW [dbo].[vHR] WITH SCHEMABINDING 
AS 
    SELECT 
        BusinessEntityID AS A,
        NationalIDNumber AS B
    FROM HumanResources.EmployeeGO

Then I ran below query to confirm that index got disappeared forever.

SELECT * FROM sys.indexes WHERE name = 'UCI_vHR'

I got nothing as a result. As soon as I figured this out, I hope to write about this again.

Indexes on Views

Tags