[dba-SQLServer] Index on view

Hello John,

I have no idea, but I would like to hear what your experiences are.

I accidentally created an index on a view recently and I cannot report on
performance, but I notice that it takes up space in the database.

I try never to join on views, I have always found that to be terribly slow.
 I prefer to write one long SQL statement with nest joins.

Do you ever use the Database Engine Tuning Advisor?  Does it collapse when
it sees your database?  I used it recently and it suggested I create a
bunch of compound indexes.  It gave me some performance increases.

Mark


On 19 June 2012 21:34, jwcolby <jwcolby at colbyconsulting.com> wrote:

> I have a pair of tables.  Both tables have a clustered index on an integer
> PK.  TableA the PK is an autonumber.  TableB the PK is the PK from TableA,
> i.e. it is 1 to 1.
>
> TableA is updated monthly, TableA and TableB both get records added
> occasionally.  I just added about 180 million records to both tables.  I do
> occasionally delete records from both as the data ages.
>
> For providing counts to my client I need to join the two tables on the PK,
> then pull a single field from TableA and a handful of fields from TableB.
>  From there I will be doing count / group by kind of things.
>
> I created a view to pull all of the fields used in these counts.  I
> thought I would create an index on the view.
>
> Is this a good idea?  I have never indexed a view but I know it is
> possible.
>
> --
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
>
> ______________________________**_________________
> dba-SQLServer mailing list
> dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com>
> http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
>
>