What’s difference between standard view and indexed view in SQL Server [closed]

Posted on

Question :

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?

Answer :

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *