Index hell again.

Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.As always, it depends. I would certainly put a fill factor < 100 - and this
depends on how frequent your inserts and defrags are. This index would
cover a query such as:
select
email
from
Customer
where
first_name = 'John'
and last_name = 'Smith'
If you're using SQL 2005, you could make email an included column and just
key on first_name, last_name.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:%23rWVJ1wYGHA.5012@.TK2MSFTNGP04.phx.gbl...
Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.

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 MalgreveYou 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
--