Using SQL Server 7 (SP4) with Access 2002 adp project.
I am trying to improve my system's performance by adding
indexes to my table. Right now, there is only one single index set
on the Surrogate PK WODetailNo of tblWorkOrderDetails.
CREATE TABLE [dbo].[tblWorkOrderDetails] (
[WODetailNo] [int] IDENTITY (1, 1) NOT NULL ,
[WONo] [int] NULL ,
[WODetailSeq] [tinyint] NULL ,
[ProductNo] [int] NULL ,
[WODetailQty] [smallint] NULL ,
[WODetailCustomerPrice] [smallmoney] NULL ,
[WODetailPrice] [smallmoney] NULL ,
[WODetailRequiredDate] [smalldatetime] NULL ,
[WODetailScheduledDate] [smalldatetime] NULL ,
[ProdConsChoiceNo] [int] NULL ,
[WODetailStatusNo] [int] NULL ,
The Natural keys are WONO + WODetailSeq, for example
Work Order No : 11111-1, 11111-2, 11111-3,....
If i run in the Query Analyzer the following:
select * from tblWorkOrderDetails WITH (NOLOCK) where WONO = 11111
then run the Perform Index Analysis, this is what SQL Server recommends to
CREATE NONCLUSTERED INDEX [tblWorkOrderDetails0] ON
[dbo].[tblWorkOrderDetails]([WONo], [WODetailNo], [WODetailSeq],
[ProductNo], [WODetailQty], [WODetailCustomerPrice], [WODetailPrice],
My questions are:
1- When creating indexes, do i create an index and put all columns that i
to index as one single index? If not, then why is SQL suggesting to create
single index and putting all these columns in one index?
2- How do i decide when to create an index. I am sure creating indexes on
all columns don't make sense.
I would appreciate any help.
Thank you very much