What happens if a table has no Primary Key?

[serge]

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 [ASSIGNUNIQUENAM E] 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 [PROCESSENTRYNOT BLTEST] 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 (@@microsoftver sion > 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_ORGANIZ ATIONNO_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

[Greg D. Moore \(Strider\)]

"serge" <se****@nospam. ehmail.com> wrote in message
news:b6******** ************@we ber.videotron.n et...

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 [ASSIGNUNIQUENAM E] 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 [PROCESSENTRYNOT BLTEST] 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 (@@microsoftver sion > 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_ORGANIZ ATIONNO_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

[Erland Sommarskog]

serge (se****@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, es****@sommarsk og.se

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

[serge]

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

No I am sorry I was wrong on this one. I just realized that I had manually
put back the PK in the table designer.

So this line did not exist before i altered the table:

CONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED
(
[UNIQUENO]
) ON [PRIMARY]
GO
Thanks for all the info. I will use the SQL Profiler to get 1 day of
workload
and then run the Index Tuning Wizard on this table and other tables.

[Erland Sommarskog]

serge (se****@nospam. ehmail.com) writes:

No I am sorry I was wrong on this one. I just realized that I had manually
put back the PK in the table designer.

So this line did not exist before i altered the table:

CONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED
(
[UNIQUENO]
) ON [PRIMARY]
GO

OK. Anyway, the table had a unique index on uniqueno, so it sounds
like the table had a primary key, although it was not declared so
formally. Who knows? Maybe a leftover from 4.x days when there were
no declared referential integrity at all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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