INDEX and VIEWS  
Author Message
Tjeerd





PostPosted: Wed Aug 17 04:07:40 CDT 2005 Top

SQL Server Developer >> INDEX and VIEWS

Hi all,

Let say that I have a table Customer

CREATE TABLE [dbo].[Customer] (
[CustomerId] [int] NOT NULL ,
[CustomerName] [nvarchar] (50),
[CustomerAge] [int] NOT NULL
) ON [PRIMARY]
GO

Let say I have an index on CustomerAge.

If I have a view defined as:

CREATE VIEW dbo.VIEWCustomer
AS
SELECT dbo.Customer.*
FROM dbo.Customer

and then if I execute the following SQL statement:

select * from VIEWCustomer where CustomerAge = 25

Will that statement use the index of the table Customer (on the field
CustomerAge) or will it not (because no index can be defined on a view)?
In other words, if a select on a view is using a WHERE clause for which
there is an index defined for the table.field defined in the view, will it
be used or not?

Best regards,

Francois Malgreve

SQL Server161  
 
 
David





PostPosted: Wed Aug 17 04:07:40 CDT 2005 Top

SQL Server Developer >> INDEX and VIEWS You can check this yourself by examining the execution plan in Query
Analyzer. The indexes certainly can be used when referencing a view in
just the same way as they are with tables.

--
David Portas
SQL Server MVP
--