Does views use table indexes.

Hi.
I have a situation where I have a table containg data, but several columns
can be used to designate that the data is "hidden". E.g. if we delete a
record, it isn't actually deleted but a datetime field called deleted_date
is changed from null to the current date-time.
Therefore I made a view like
create view vw_LiveData as select * from Data where deleted_date is null
The original table had a full-text indexed column, but using that search
criteria on the view didn't work
select * from vw_LiveData where contains(fullTextField, 'blahblahblah')
The line produces an error saying that the view doesn't have a fulltext
index.
What I speculate now is all the other indexes defined on the table, do they
have any importance when searching through data in the view?
Suppose the Data table has a foreign key to a user called User_ID that is
indexed on the table
Clearly the index is used when I type
Select * from Data where User_ID=@.User_ID
Is it also used when I type
select * from vw_LiveData where User_ID=@.User_ID
or is it necessary to created index views if I want both easy to read sql
code AND acceptable performance?
Thanks in advance,
Peter StrimanPeter Striman wrote:
> Hi.
> I have a situation where I have a table containg data, but several
> columns can be used to designate that the data is "hidden". E.g. if
> we delete a record, it isn't actually deleted but a datetime field
> called deleted_date is changed from null to the current date-time.
> Therefore I made a view like
> create view vw_LiveData as select * from Data where deleted_date is
> null
> The original table had a full-text indexed column, but using that
> search criteria on the view didn't work
> select * from vw_LiveData where contains(fullTextField,
> 'blahblahblah')
> The line produces an error saying that the view doesn't have a
> fulltext index.
> What I speculate now is all the other indexes defined on the table,
> do they have any importance when searching through data in the view?
> Suppose the Data table has a foreign key to a user called User_ID
> that is indexed on the table
> Clearly the index is used when I type
> Select * from Data where User_ID=@.User_ID
> Is it also used when I type
> select * from vw_LiveData where User_ID=@.User_ID
> or is it necessary to created index views if I want both easy to read
> sql code AND acceptable performance?
No. The index is used. It may be different with full text indexes -
check with BOL. Btw, IMHO it's better to name all columns in the view
explicitely. That way your view does not change if the underlying table
changes.
Kind regards
robert|||Thanks for the reply, just what I wanted to hear :)
It is different with full-text indexes - and that was what gave me the
concern. But it's no issue in my case since I would only use the
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Peter Striman wrote:
> No. The index is used. It may be different with full text indexes -
> check with BOL. Btw, IMHO it's better to name all columns in the view
> explicitely. That way your view does not change if the underlying table
> changes.
> Kind regards
> robert
>|||
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:<#F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl>...
<snip>
Btw, IMHO it's better to name all columns in the view

> explicitely. That way your view does not change if the underlying table

> changes.

>

> Kind regards

>

> robert

>
I STRONGLY agree. A view using a SELECT * will do very strange things if
fields are added to the table.
Daniel Wilson <Mailto://d.wilson@.embtrak.com>
Senior Software Solutions Developer
Embtrak <http://www.Embtrak.com> Development Team
DVBrown Company
(864)292-5888