Posted by Erland Sommarskog on 06/18/05 12:03

serge (sergea@nospam.ehmail.com) writes:
> 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?

It's impossible to tell a priori. Some of the indexes would be a waste
of disk space. Or it could be that you should add more indexes to
this table. And it could be the case, that some indexes should be
replaced with others.

The only way to find out is to investigate how the table is being accessed.
On way to do this is to catch a day's workload with the Profiler, and
the feed the Index Tuning Wizard with the data. If ITW suggests that
some indexes should be dropped, you need make further research, as these
indexes could be essential for end-of-the-month reports and similar.

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

It follows from the above, that the answer is: no, I can't tell.

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

So this table apparently has a primary key, it is just the that the
Table Designer in EM fails to recongnize it. I might be that it
gets tricked by the fact that there is both a clustered and a non-
clustered index on UNIQUENO, and the primary-key index is non-clustered.

And, again, having both a clustered and a non-clustered index on a
column, may or may not make sense.

I would advise you to stay away from the Table Designer. There are
several serious flaws with it, not the least when it comes to modifying
existing tables.

Rather than relying on the crappy table designer, use sp_help or
sp_helpindex find the keys of a table.

> If that is the case, then what is the difference between
> PRIMARY KEY NONCLUSTERED
> and
> PRIMARY KEY CLUSTERED?

In SQL Server a primary key is always impletemented as an index. An
index can be clustered or non-clustered.

As for having no primary key, not having a primary key for a permanent
table should be an exception. But I have a table in the database I
work with that does not have any PK or UNIQUE constraint. I could
create a unique constraint on it, but it would be a insane number of
columns, and be of little use. One should know here that this table is
"transient". That is the table definition is permanent, but it's used
in the interface for an important cacluation routine, and data that
gets entered here, is deleted once it has been used.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]