When a view is given a unique clustered index, SQL Server materialises its output and maintains it much as it would a table. Creating one requires SCHEMABINDING plus particular SET options, and changes to the underlying base tables are applied to it too.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Indexed view in context, with comparison tables and the common traps.
Terms in this definition
- View
Shows a system through one chosen group of related concerns. No view is generic; each one is tied to whichever architecture it depicts.
- Index
Speeds up queries that filter on certain columns by keeping those columns sorted, with pointers back to each row; the price is more storage and slower writes.
- SQL Server
The relational database from Microsoft that organisations host and run themselves, in their own data centres or on VMs. Its engine also sits underneath the Azure SQL services and SQL database in Fabric.
- Event
Table in Log Analytics where entries from Windows event logs are kept.
- SCHEMABINDING
Locks an object to the schemas it depends on. Security policies default to ON, meaning nobody's rights on their predicate function get checked; turned OFF, each user requires SELECT or EXECUTE over that function plus anything it touches.
- Set
Secret permission in Key Vault for writing secrets; some older material refers to it as Create.
- Table options
Set only at creation with
OPTIONS, these key-value storage settings can't be altered or dropped afterwards; on Delta tables they also show up among the table properties.
Related terms
- Full-text index
Built with CREATE FULLTEXT INDEX, this token-based index covers xml, character or varbinary(max) columns in a table or indexed view. Each table can have just one, and it needs a single-column, unique, non-nullable key index.
- NOEXPAND
Tells the optimiser to read an indexed view through its own index rather than substituting the view's definition. Standard edition requires this hint, while OPTION (EXPAND VIEWS) works the other way round.