March 27, 2013

Indexes on Views

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

USE AdventureWorks2012
GO

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

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.Employee
GO

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

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

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

No comments:

Post a Comment