How to Add Invisible Indexes in SQL Server Enterprise Edition

Every now and then, a client says, “We want to add indexes to this third party application, but if we do, we’ll lose support.”

No problem – enter indexed views.

Say our vendor app is the Stack Overflow database, and the app constantly queries Users by display name:

USE StackOverflow; GO DropIndexes; SET STATISTICS IO ON; GO SELECT * FROM dbo.Users WHERE DisplayName = N’Brent Ozar’;

That query plan scans the Users table because there’s no index on DisplayName:

And our mean ol’ nasty vendor won’t let us add any indexes. No problem – let’s add a view: