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?

  • 1
    This can be solved by looking at any number of web resources. This does not need the input of a database administrator. Additionally, the question is vague and confusing – jcolebrand Mar 20 '12 at 14:52
  • 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

Other notes:

  • 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

Not the answer you're looking for? Browse other questions tagged or ask your own question.