Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation.
- Security: we create views so that a user can read specific columns out of certain tables, but not all the data.
- Simplification: sometimes, it’s easier to write a SELECT against one view than a SELECT against multiple tables, with joins – repeatedly.
- Aggregation: again, it can be easier to SELECT already-aggregated data than to write the query – repeatedly.
The down side to views is that when you query them, you’re still reading data from all of the underlying tables. This can result in large amounts of I/O, depending on the number and size of tables. However, you can create a unique clustered index on the view – referred to as an indexed view – to persist the data on disk. This index can then be used for reads, reducing the amount of I/O.
There are some limitations to indexed views, but when you can create one and it improves performance, it really improves performance. But, as with all features in SQL Server, indexed views aren’t the answer to everything. Here’s a quick look at some things you can and can’t do with them.
The view definition can reference one or more tables in the same database.
Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.
The view definition can’t reference other views, or tables in other databases.
It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.
Choose the Right Tool
If you’re looking to have a complicated, aggregated query persisted to disk to reduce I/O, an indexed view may be the right tool for your job. Test it as an option, and if it works, put it to use!
Learn more about indexed views in my video The Okapis of SQL Server Indexes!