Posted by Greg D. Moore \(Strider\) on 06/18/05 04:46

"serge" <sergea@nospam.ehmail.com> wrote in message
news:b6Kse.19443$Sl1.371521@weber.videotron.net...
> 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!


Then it is nota table. At best it's a heap.


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

Well, the question doesn't make a lot of sense. 1/3 of the table's fields
as indexes doesn't make sense.

But getting to what you really seem to mean... if they are appropriate, 9
indices can be fine. (updates will be slower though.)


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

Nope, not w/o knowing the table design AND how it's accessed.


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

Not sure why EM is showing you what it is.

As for clustered vs. non-clustered, that has to do with how the data is
stored on the disk.

A table can have only ONE clustered index (which may or may not be the
primary key).

In a clustered index, the root nodes are the data.

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