Re: index on a view
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/21/05
- Next message: grumy: "massive log file"
- Previous message: Angelita Spear: "Re: query assistance -return most recent date"
- In reply to: cmarano: "Re: index on a view"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Feb 2005 11:28:33 +0100
On Fri, 18 Feb 2005 14:58:31 GMT, cmarano wrote:
>Hello,
>
>I have two applications !
>
>In one i have a tabel : TAB with 2 fields cod and descr , length 3 and 60
>( varchar)
>
>In the other application i have to read e write in this table , but i have
>different field name :
>cod1 and des (alias). I repair to this with a view .
>
>Now i have to chain this table with key (cod1) this is the problem.
(snip)
Hi Carlo,
Thanks for posting your explanation and the CREATE TABLE and CREATE VIEW
statements.
In this case, there is no reason to index the view. In fact: if you do,
SQL Server will have to maintain a copy of all data in the table and
change that copy whenever the data in the table changes. You double the
storage space required, give SQL Server extra work to do on updates and
you gain nothing from it.
To be able to use your other application without changing it, you just
need a normal (non-indexed) view. In queries, SQL Server will substitute
the view's name with the view's definition (and since that is simple, it
comes at no extra cost). Similar, updates to the view will be translated
back into updates to the table - and again, at a performance price you
won't notice, since it's a very simple one-on-one translation.
You also don't need the TOP 100 PERCENT in the view (you'll get all rows
by default - only use TOP if you want less than all rows) and you should
remove the ORDER BY (it's not guaranteed to work anyway - it'll be only
used to determine which rows are or are not in the TOP 100 PERCENT, not to
determine the ordering of rows returned by the view).
CREATE VIEW dbo.TABCE05F
AS
SELECT cod_zona AS T5COAR, des_zona AS T5DEAR
FROM dbo.CO_ZONE
GO
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: grumy: "massive log file"
- Previous message: Angelita Spear: "Re: query assistance -return most recent date"
- In reply to: cmarano: "Re: index on a view"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
