Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

share|improve this question
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

closed as not a real question by jcolebrand♦ Mar 20 '12 at 14:51

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

1 Answer

up vote 10 down vote
  • 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

share|improve this answer

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