Indexes versus views

I am currently taking a class, SQL SErver 2000 Database Design and
Implementation (70-229) and have just finished reading about indexes. In
terms of their primary use, these seem closely related to 'views'. I am
unable to find any research on how to know when to use a 'view' versus an
'index'. I was disappointed that the text did not compare the two for
contrast. Can someone explain or direct me to information regarding their
differences?Indexes and views don't really have anything in common.
A view is a stored query that can be accessed like a table.
An index is sorted list of columns from your table (or indexed view, but
thats a different topic). This sorted list allows the database to quickly
locate a particular value in the indexed columns, and includes a pointer to
the actual row in the table. Indexes are used to speed up searches.
Unique indexes not only speed up searches but prevent duplicate values from
being inserted into the table.
A search on Google (or any good database book) will turn up much more in
depth explanations.
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
> I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||Did the instructor explain these two concepts? They are nothing at all
alike, and their use is quite different, so I would be surprised to see a
section comparing the two. It would be like having a section explaining the
difference between tables and SELECT statements.
An index is a physical structure that contains pointers to the data and
helps find the data you are looking for more quickly. In general, you don't
'use' an index. You create the indexes you need, and SQL Server chooses
whether or not to use them to help get to the data you are looking for.
A view is a way to save a SELECT statement so you don't have to retype it
every time, and can use the data returned by the view as if it were a table.
If there are indexes on the table that your view is based on, they can be
used exactly as if you were using the full underlying SELECT instead of
using the view.
You can also build indexes on views, but that is a whole separate big topic.
Have you asked the instructor this question?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||There are indexes, there are views, and there are indexed views, so
lets take them one by one.
An index is used for accessing a table efficiently. It may also serve
to enforce a UNIQUE constraint. It is a physical, rather than logical
concept. Dropping an index may result in a query running slower, but
it should never change the results a query returns.
A view is, in contrast, strictly logical. The result set from any
SELECT command is (effectively) a table; a view simply provides a
convenient way to reference the results of a SELECT as if it were a
physical table.
As you can see, there is nothing about indexes and views that is
closely related. But, I suspect the source of your confusion is that
odd creation, the indexed view.
An indexed view is a trick. While a view is strictly a logical
construct, and indexed view is a sneaky way to force the view to
become a physical table internally. Like a real view, there is never
any question about it being kept in sync with the table(s) from which
it is derived.
The indexed view is a specialized tool used to optimize retrieval.
Indexed views can sometimes cause updates to the underlying table(s)
to become horribly slow, so they are not a tool one chooses to
implement lightly.
There are also some issues about how the optimizer treats them,
depending on the edition (Standard or Enterprise) running, but to get
started just try to understand the basics.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
<apridgen@.discussions.microsoft.com> wrote:

>I am currently taking a class, SQL SErver 2000 Database Design and
>Implementation (70-229) and have just finished reading about indexes. In
>terms of their primary use, these seem closely related to 'views'. I am
>unable to find any research on how to know when to use a 'view' versus an
>'index'. I was disappointed that the text did not compare the two for
>contrast. Can someone explain or direct me to information regarding their
>differences?|||> I was disappointed that the text did not compare the two for
> contrast.
Well, this is kind of like expecting a car manual to compare anti-freeze and
carburetors, or seat belts and radios, for contrast. They are completely
different things, even though they are part of a bigger entity, as others
have and will explain(ed).
A|||Well, you see, that's the 'rub', I have no instructor...only a book and
online tutorial....sometimes concepts are hard to grasp with no one to
'bounce' questions off of.
I was about the 'views'. I incorrectly thought they were some
manipulation of the data (such as a logical file arrangement).
Thank you for the explanation...it really did help.
Some of these 'concepts' are really hard for me, as I have been using RPG400
FOREVER!!!
"Kalen Delaney" wrote:

> Did the instructor explain these two concepts? They are nothing at all
> alike, and their use is quite different, so I would be surprised to see a
> section comparing the two. It would be like having a section explaining th
e
> difference between tables and SELECT statements.
> An index is a physical structure that contains pointers to the data and
> helps find the data you are looking for more quickly. In general, you don'
t
> 'use' an index. You create the indexes you need, and SQL Server chooses
> whether or not to use them to help get to the data you are looking for.
> A view is a way to save a SELECT statement so you don't have to retype it
> every time, and can use the data returned by the view as if it were a tabl
e.
> If there are indexes on the table that your view is based on, they can be
> used exactly as if you were using the full underlying SELECT instead of
> using the view.
> You can also build indexes on views, but that is a whole separate big topi
c.
> Have you asked the instructor this question?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
> news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>
>|||Well, thanks so much for boosting my confidence.
I now remember why I hestitated before posting for help!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, this is kind of like expecting a car manual to compare anti-freeze a
nd
> carburetors, or seat belts and radios, for contrast. They are completely
> different things, even though they are part of a bigger entity, as others
> have and will explain(ed).
> A
>
>|||Thanks for the help...when looking for 'help' I always hope for people
willing to share knowledge without being condescending.
"Roy Harvey" wrote:

> There are indexes, there are views, and there are indexed views, so
> lets take them one by one.
> An index is used for accessing a table efficiently. It may also serve
> to enforce a UNIQUE constraint. It is a physical, rather than logical
> concept. Dropping an index may result in a query running slower, but
> it should never change the results a query returns.
> A view is, in contrast, strictly logical. The result set from any
> SELECT command is (effectively) a table; a view simply provides a
> convenient way to reference the results of a SELECT as if it were a
> physical table.
> As you can see, there is nothing about indexes and views that is
> closely related. But, I suspect the source of your confusion is that
> odd creation, the indexed view.
> An indexed view is a trick. While a view is strictly a logical
> construct, and indexed view is a sneaky way to force the view to
> become a physical table internally. Like a real view, there is never
> any question about it being kept in sync with the table(s) from which
> it is derived.
> The indexed view is a specialized tool used to optimize retrieval.
> Indexed views can sometimes cause updates to the underlying table(s)
> to become horribly slow, so they are not a tool one chooses to
> implement lightly.
> There are also some issues about how the optimizer treats them,
> depending on the edition (Standard or Enterprise) running, but to get
> started just try to understand the basics.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
> <apridgen@.discussions.microsoft.com> wrote:
>
>|||> Well, thanks so much for boosting my confidence.
> I now remember why I hestitated before posting for help!
Hey, you get what you pay for. Since other people had already explained the
concepts directly, I just thought I'd show an analogy of your expectations.
Sorry I bothered, because it seems my point was lost on you.