Indexes on Views
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.