Indexed views -- sql server new users

sql server new users : Indexed views

Gail Erickson [MS]
10/10/2005 3:33:48 PM

Hi Rob,
This link to the white paper on indexed views should answer your questions
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx.

When a view (nonindexed) is stored in the database, only the metadata is
stored. That is, the view definition is stored and when you reference that
view in a statement like SELECT, the data is materialized at run time.
After a unique clustered index is created on the view, the view's result set
is materialized immediately and persisted in physical storage in the
database (just like a table), saving the overhead of performing this costly
operation at execution time.

[quoted text, click to view]
Yeah, pretty much. The query can reference the indexed view directly, or,
more importantly, the query optimizer can select the view if it determines
that the view can be substituted for some or all of the query in the
lowest-cost query plan. In the second case, the indexed view is used instead
of the underlying tables and their ordinary indexes. The view does not need
to be referenced in the query for the query optimizer to use it during query
execution. This allows existing applications to benefit from the newly
created indexed views without changing those applications.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

[quoted text, click to view]

Rob Oldfield
10/10/2005 10:45:11 PM

....just what are they and how do they work? I've tried looking at SQL (2K)
help but can't find anything that really explains them.

For background, I'm coming from the angle of writing stuff in VB.Net, using
SQL as my data store. I understand the use of indexes in tables but over
the weekend I ran my db through the index tuning wizard and it made various
recomendations, including the creation of an indexed view. I put those
changes in place (and saw a very nice increase in performance) but I just
don't get it - the .Net apps, as far as I can see, just run specific SQL
statements. This new indexed view isn't referenced in any of those, so how
does it help to speed things up?

Does it think to itself... "oh look, here's this SELECT command - I know
that I can get at the results more quickly if I use the view"... or what?

Any explanations, or links to explanations, would be greatfully received.

Rob Oldfield
10/11/2005 12:25:51 AM

[quoted text, click to view]

Aaaah. That makes sense now. Why don't they say that in the documentation?

Thanks to Gail and Hugo. I owe you both a beer.

Hugo Kornelis
10/11/2005 12:52:53 AM

[quoted text, click to view]

Hi Rob,

A normal (non-indexed) view is essentially just a stored query
definition. The results of the view are not stored. Each time the view
is referenced, the query that defines the view is executed and the
results are used.

(Or rather - the query definition that makes up the view is inserted
into the query that uses the view, in place of the view; the resulting
completed query is then fed to the optimizer, that will work out the
best way to satisfy the request).

In some cases, it makes sense to tell SQL Server to choose a different
strategy: calculate the results of the view and keep them in permanent
storage (known as "materializing" the view), and make sure that each
change to the underlying base tables gets reflected in the stored view
results. Now, when the view is referenced, SQL Server can just use the
stored results of the view instead of having to recompute the whole
thing. A common situation where this is used is in aggregates: one could
imagine a bank storing all deposits and withdrawals since the opening of
a savings account, and having a materialized view to hold the current
balance.

Anyway - if you want SQL Server to materialize the view, you do that by
defining a unique nonclustered index for it. That's why a materialized
view is more commonly known as indexed view. (And for the record - once
the first, clustered index is created, you can go on to create addition
nonclustered indexes on the indexed view, if you like).

[quoted text, click to view]

Yes, that's exactly what the optimizer thinks. If you are running
Enterprise Edition, that is. Other editions will only use the indexed
view if it's explicitly used in the query.

Best, Hugo
--

Rob Oldfield
10/11/2005 12:56:17 AM

Though that does also raise another few questions for me... I may be back
with more requests for info once I get back at my server.

Gail Erickson [MS]
10/11/2005 11:55:39 AM

I'll take a shot at a couple of them.

[quoted text, click to view]

Restarting the server does not cause the view to materialize. The view is
materialized once and only once. This happens when you create the unique
clustered index on the view. From that point on, you can think of the view
as a table (or perhaps a virtual table would be more accurate). That is,
the data is physically persisted on the disk. When data modifications are
made to the underlying table(s), the indexed view is automatically updated.
There are some maintenace implications here if the the underlying table data
is frequently modified.

[quoted text, click to view]
Yes, that can be an outcome of an indexed view. Remember that the result set
of the view definition (the data) is physically stored. So if subsequent
queries are covered by the indexed view (that is, all the required data is
available in the view), than the query processor doesn't even need to access
the base tables.

I haven't tried creating an indexed view that references a remote database.
But since a SELECT statement can contain a 4-part name reference
(server.database.schema.table), it seems like that might be a possibility.
I doubt that the Index Tuning Wizard will be able to help you with that.

I'm not sure what your problem is with the Manage Indexes grayed out.
Assuming you can connect to the database, you might try writing your own
CREATE VIEW and CREATE INDEX statements against that database (use Query
Analyzer). Take a look at the numerous examples in the white paper for
ideas.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

[quoted text, click to view]

Rob Oldfield
10/11/2005 12:40:31 PM

OK. Some more queries, hope I get some equally helpful answers...

If I have a very complex indexed view, and then restart the server, does
that view get materialized automatically, or does it wait for the first
person to actually use a command that references it? i.e. can the first
person using it after a restart expect a long delay while it rebuilds?

A lot of the work that I do references an Oracle database linked in to SQL,
which can be horribly slow. (It's a 3rd party db, and horribly out of date,
so linking is the only way I've found to access the data in .Net) I was
hoping that SQL might be able to create indexed views on that data but the
wizard didn't come up with anything. Is it just impossible to create that
kind of indexed view, is there any way to force the wizard to do it, or can
it be done manually?

The tables that are joined in the indexed view that the wizard came up with
for me actually derive from another users database. I have a method in
place of importing those (two) tables from his Access db into SQL. The link
between those tables is not well designed, it's a one to many based on a
straight text field in both. I already had indexes on that field on both
tables before running the wizard. Is it typical of indexed views that they
will help where the db structure is not as good as it could be?

On the subject of aggregating views, I have another database that does
actually contain many of them. If I throw that one at the index tuning
wizard, then it doesn't suggest any indexed views. I'm not sure whether
this is actually a permissions issue as when I try to use the Manage Indexes
command on an existing view, it's greyed out. Having said that, it's also
greyed out in the other db where the wizard *did* create one. This is
purely on SQL data and I'm logging in as sa on both, where sa is the owner
of each of the dbs.

Thanks again for any further help.

Gail Erickson [MS]
10/11/2005 2:10:11 PM

Yeah, I don't know what I was thinking. The paper also lists the following
requirement:
The view, and all tables referenced in the view, must be in the same
database and have the same owner.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

[quoted text, click to view]

Rob Oldfield
10/11/2005 9:20:45 PM

I have just read through the white paper (though I'd guess I'd need to do it
while running some tests at the same time to get fully into it) and it does
seem to be a good practical guide to how they work (or not, at the moment.
The one that I had created I had to delete today - it was giving ARITHABORT
errors when attempting to run update commands - I'll definitely be reading
that part of the paper more thoroughly in the near future). On the linked
Oracle tables it does include "Tables must be referenced by the view using
two-part names (schemaname.tablename)." as a view requirement, which makes
me think that they may not be possible. But I can always give it a try of
course.

Thanks again Gail. It's much appreciated.

Rob Oldfield
10/11/2005 11:50:18 PM

Looks like I'm stuck waiting for them to update their database then. Ah
well.

[quoted text, click to view]

Hugo Kornelis
10/12/2005 11:58:19 PM

[quoted text, click to view]

Hi Rob,

The view is materialized as soon as you create a clustered index on it.
The data is then stored and kept just as data in normal tables is.

I an older version of SQL Server, I once created a table and filled it
with the results of a query on another table. I then installed triggers
on the other table that would change the contents of the new table, such
that it remained correct. I used this table to generate reports with
some hefty aggregates very fast.
The indexed view is basically the same, but now I don't have to make the
triggers anymore. It's SQL Server that makes them (well, not real
triggers of course but you get my point).

[quoted text, click to view]

There are many limitations on what you can and can't do in an indexed
view. Refering to other databases is one of them. For a full list,
search for CREATE UNIQUE CLUISTERED INDEX in Books Online.

(snip)
[quoted text, click to view]

I never use Enterprise Manager to manage my views. I'm addicted to Query
Analyzer for all DB work. It's some extra typing, but it gives me much
more control.

If I have to make a guess, I'd say that the option is greyed out because
the view violates one of the many limitations for indexed views.

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn