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:
CREATE OR ALTER