Posted by serge on 06/18/05 03:42

I ran into a table that is used a lot. Well less than
100,000 records. Maybe not a lot of records but i believe
this table is used often. The table has 26 fields, 9 indexes
but no Primary Key at all!

There are no table relationships defined in this database, no
Natural keys, only Surrogate keys in the database.

1- Maybe an odd question but is it normal to have 1/3 of the
table's fields as indexes? Is this a valid question or it really
doesn't matter if you have 9 indexes if they are appropriate to be
indexes?

2- Below is the DDL of the indexes (Is DDL the appropriate term
to describe the indexes?) Without going into too technical about
what the table is, what relationships it has with other tables,
would you be able to tell if the indexes are good, bad, too many,
etc?

3- If i open the table in DESIGN view in SQL EM, i don't see
the Primary key icon. Yet here i see the words "PRIMARY KEY
NONCLUSTERED". Does this mean UNIQUENO is actually some type
of primary key? If it was CLUSTERED then SQL EM would show
UNIQUEID with a key to the left it identifying it as a PK?
If that is the case, then what is the difference between
PRIMARY KEY NONCLUSTERED
and
PRIMARY KEY CLUSTERED?


CREATE UNIQUE CLUSTERED INDEX [TBLTEST_PK] ON
[dbo].[TBLTEST]([UNIQUENO]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED
(
[UNIQUENO]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ASSIGNUNIQUENAME] ON
[dbo].[TBLTEST]([USERNO], [STARTDATE], [NAME]) WITH
FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [ENDDATE] ON [dbo].[TBLTEST]
([ENDDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IUSERASSIGNACT] ON
[dbo].[TBLTEST]([USERNO], [TASKNO], [PROCESSENTRYNO])
WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [STARTDATE] ON [dbo].[TBLTEST]
([STARTDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [PROCESSENTRYNOTBLTEST] ON
[dbo].[TBLTEST]([PROCESSENTRYNO]) WITH
FILLFACTOR = 80 ON [PRIMARY]
GO

/****** The index created by the following statement
is for internal use only. ******/
/****** It is not a real index but exists as
statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_NAME] ON
[dbo].[TBLTEST] ([NAME]) ')
GO

CREATE INDEX [TASKNO_IDX] ON [dbo].[TBLTEST]
([TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [TBLTEST_ORGANIZATIONNO_IDX] ON
[dbo].[TBLTEST]([ORGANIZATIONNO]) WITH
FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [TASKNOUSERNO] ON [dbo].[TBLTEST]
([USERNO], [TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO



Thank you

[Back to original message]