I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don’t?
Indexed view needs to be bind with schema object.I am also confused about the point that ‘*’ is not allow in the schema-bind object.For instance:
create view viewname as select * from tablename
The statement above can not work. why?
- An indexed view is physically stored (“materialised”) on disk = requires memory
- A standard view is simply an expandable macro: there is no persistence of the data and the base tables are always used
- Both will return the correct data from the base tables
- SQL Server will consider whether to use the indexed view or just expand it like a macro (subject to edition and NOEXPAND hint)
SELECT * is bad practice anyway, but
* means you can’t qualify all columns as required for indexed views