index on a view

Hello,
I have a table (TAB) and A View with alias (VIEW)
Table cod varchar 3
descr carchar 60
my view cod alias COd1
descr alias DES
Now i need a index on view with key COD1
I can't create it.
Can you help me
Carlo
On Thu, 17 Feb 2005 15:48:06 GMT, cmarano wrote:

>Hello,
>I have a table (TAB) and A View with alias (VIEW)
>Table cod varchar 3
> descr carchar 60
>
>my view cod alias COd1
> descr alias DES
>Now i need a index on view with key COD1
>I can't create it.
>Can you help me
Hi Carlo,
I think I can help you, but first I need to get a better picture of what
you're trying to achieve.
What I read from your message is that you have a view that is simply the
same as your table, but with different column names, and that you are now
trying to index that view. I hope that I have misread you, though, as this
would simply result in the same data redundantly being stored at two
different locations in the database.
The best way to clarify your problem is to post:
* Actual table structure, as CREATE TABLE statements - please include all
constraints and all properties (see www.aspfaq.com/5006)
* Some rows of illustrative sample data to give me an idea of the kind of
data you're handling (posted as INSERT statements)
* The CREATE VIEW statement used to create the view you want to index
* The reason for wanting to index your view (in other words: what are you
hoping to achieve)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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.
the create statment :
CREATE TABLE [CO_ZONE] (
[cod_zona] [varchar] (3) COLLATE Latin1_General_BIN NOT NULL ,
[des_zona] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[dat_obsoleto] [datetime] NULL ,
[prg_net] [timestamp] NOT NULL ,
CONSTRAINT [XPKCO_ZONE] PRIMARY KEY CLUSTERED
(
[cod_zona]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Table contents : IT Italy
US U.S.A.
.. ......
Creat view statments:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.TABCE05F
WITH SCHEMABINDING
AS
SELECT TOP 100 PERCENT cod_zona AS T5COAR, des_zona AS T5DEAR
FROM dbo.CO_ZONE
ORDER BY cod_zona
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Best regards, Carlo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> ha scritto nel messaggio
news:h6t911p41hpuus9vmnts2vfd073a7v1ffk@.4ax.com...
> On Thu, 17 Feb 2005 15:48:06 GMT, cmarano wrote:
>
> Hi Carlo,
> I think I can help you, but first I need to get a better picture of what
> you're trying to achieve.
> What I read from your message is that you have a view that is simply the
> same as your table, but with different column names, and that you are now
> trying to index that view. I hope that I have misread you, though, as this
> would simply result in the same data redundantly being stored at two
> different locations in the database.
> The best way to clarify your problem is to post:
> * Actual table structure, as CREATE TABLE statements - please include all
> constraints and all properties (see www.aspfaq.com/5006)
> * Some rows of illustrative sample data to give me an idea of the kind of
> data you're handling (posted as INSERT statements)
> * The CREATE VIEW statement used to create the view you want to index
> * The reason for wanting to index your view (in other words: what are you
> hoping to achieve)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||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)