473,695 Members | 2,767 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What happens if a table has no Primary Key?

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
Jul 23 '05 #1
4 10558

"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

Jul 23 '05 #2
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
Jul 23 '05 #3
> > 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.

Jul 23 '05 #4
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
Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

52
6417
by: Tony Marston | last post by:
Several months ago I started a thread with the title "What is/is not considered to be good OO programming" which started a long and interesting discussion. I have condensed the arguments into a single article which can be viewed at http://www.tonymarston.net/php-mysql/good-bad-oop.html I fully expect this to be the start of another flame war, so sharpen your knives and get stuck in!
112
10317
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please, share your experience in using IDENTITY as PK .
9
2759
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with each having 3 fields each, their own PK; the FK back to the parent table; and the unique data for that table. There is a one to many relation between the parent and each of the 9 child rows. Each child table has between 100,000 and 300,000
14
4286
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to salvage the records from the many table and without going into detail, one of the reasons I can't do the opposite as there are records in the ONE table that I need to keep even if they don't have any child records in the MANY table. Below I created...
7
1979
by: robertbrown1971 | last post by:
I just inherited a Java application with a fairly complex data model that does not yet have any indexes except those on primary keys. It is still in development and before I get to do any performance testing I want to put some default indexes on it. I realize that this a very vague request but it's better than nothing so I want to start with those that are generally a good idea and then tune those areas that require more fine grained...
6
2494
by: Andreas | last post by:
Hello list, what about uniqueness of inherited primary keys ? eg you have : create table objects ( id int4, date_created timestamp(0), primary key (id)
4
9164
by: Fred | last post by:
Hi. What is the usual and what are the possible fields to use for the primary key of an intersecting table of a many-to-many relationship? I would think the typical, most common fields would be to set the intersecting table to have its own unique primary key. Right? OR
669
25911
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic paper written on this subject. On the Expressive Power of Programming Languages, by Matthias Felleisen, 1990. http://www.ccs.neu.edu/home/cobbe/pl-seminar-jr/notes/2003-sep-26/expressive-slides.pdf
1
1508
by: Mark | last post by:
Hi there gurus, can you please add your 2 cents on this design? We're having trouble relating these tables in a diagram because of the keys. Is it necesary to have the references setup? I would assume yes so the forign keys can be setup. If you look at this link, you'll see our diagram. In Red are the relationships that we would like to make for referential integrity, but cannot because of the keys....
0
8555
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8971
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8817
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7651
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6484
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5831
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4571
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.