We're migrating a production database (mission critical) from RMS on Aplha to
SQL
Server 2000.

The data is static, it only gets updated once every 24 hrs. Some tables only
once a week. Apart from the updates this is a read-only database with rather
tight performace requirements.

The database is organized with one table (MAIN) in the middle of a
star-diagram. This table has a primary key. All the referencing tables only
have a foreign key, no primary key. The indexes are all fine and tuned.

My question is: according to SQL Server Best Practices Analyzer I should
have primary keys on all tables, do I really need this? The database design
is carried over from RMS. The realtionships from MAIN to the other tables are
all one - to - zero-or-more realtions.

BTW, thanks for great answers to my other questions earlier!
___________________________

- B@rney

The obvious question is why *wouldn't* you want a primary key? Keys are
critical to ensuring correct results from the database and an important
factor in optimizing performance too.

SQL Server enforces constraints through indexes so a UNIQUE
non-nullable index is logically equivalent to a PRIMARY KEY constraint.
If you have a UNIQUE non-nullable index then there is no need to
declare a PRIMARY KEY constraint on those same columns - it's just a
matter of convention to create one unique key as a PRIMARY KEY
constraint. The important thing though is that you have a primary key
enforced either through an index or a constraint.

I'm at a customer site. They've allready ported the database, I'm just giving
some advice towards optimalization. And, I've never seem a database without
Primary Keys like this. But, they've got approx 30GB data in the database,
adding the PK's now would be a big job. Not to mention the 50 something
applications reading the data.

I understand that "keys are critical to ensuring correct results", but
that's not the issue here. The database is working as it should. The data in
the referenced table are of such a character that it's allright if there are
zero or more records.

So, I guess, the main issue remaining is that of performance.
We have been through index tuning, and have allready seen some performance
gains here. But you mention that not having a PK might affect performance. In
what way?

Show quoteHide quote

I can think of two main ways that keys make a difference to
performance. A query on a unique index may perform better than a
similar operation on the same data with a non-unique index because the
uniqueness constraint gives the optimizer more information to decide on
the best query plan. The second reason is that if you are carrying
redundant duplicate data in the table then you have more data = more
physical reads = poorer performance. If you didn't have redundant data
you would create the key, right?

Thx for the quick replies David!

Well, we don't have redundant data. But, as mentioned, we have a system
that's up and running with a few million records in it.

I think maybe I was too eager and didn't explain. I'll let some code do the
talking:

CREATE TABLE [MAIN] (
    [OBJNR] [int] NOT NULL ,
    [DATE] [int] NULL ,
    [ORGNR] [char] (11) NULL ,
    [ORGNAME] [char] (36) NULL ,
    [STREETADR] [char] (30) NULL ,
    CONSTRAINT [PK_MAIN] PRIMARY KEY  CLUSTERED
    (
        [OBJNR]
    )  ON [PRIMARY] ,
    CONSTRAINT [CKC_OBJNR_MAIN] CHECK ([OBJNR] >= (-9999999) and [OBJNR] <= 0)
)

CREATE TABLE [NOTICES] (
    [OBJNR] [int] NOT NULL ,
    [DATE] [int] NULL ,
    [REGDATE] [int] NOT NULL ,
    [BTYP] [char] (3) NOT NULL ,
    [BKIL] [char] (4) NULL ,
    [BSTA] [char] (1) NOT NULL ,
    [BINR] [char] (12) NULL ,
    CONSTRAINT [FK_NOTICES_MAIN] FOREIGN KEY
    (
        [OBJNR]
    ) REFERENCES [MAIN] (
        [OBJNR]
    ) ON DELETE CASCADE
)

One record in MAIN might have zero, one, or more records in NOTICES.

As you can see NOTICES have no PK.

It has two indexes:
CREATE  INDEX [OBJNR_NOTICES_IDX] ON [dbo].[NOTICES]([OBJNR])
CREATE  INDEX [OBJNR_REGDATE_NOTICES_IDX] ON [dbo].[BETANM]([OBJNR],
[REGDATE])

And a query for data in NOTICES can be something like this:

SELECT     NOTICES.OBJNR, NOTICES.REGDATE, NOTICES.BTYP, NOTICES.BKIL,
NOTICES.BSTA, NOTICES.BINR, TYPE.ART
FROM         NOTICES INNER JOIN
                      MAIN ON NOTICES.OBJNR = MAIN.OBJNR LEFT OUTER JOIN
                      TYPE ON NOTICES.BTYP = TYPE.CODE
WHERE     (NOTICES.BSTA = 'D' OR
                      NOTICES.BSTA = 'O' OR
                      NOTICES.BSTA = '') AND (MAIN.OBJNR = '-7951')
ORDER BY NOTICES.REDAT DESC

In this case, do I need to add the PK's? It will be a couple of extra days
work, something I don't mind beeing a consultant, but I'd rather not do it if
it's not really neccessary.

Show quoteHide quote

You haven't said what column(s) you would propose to create the PK on.
Are you aware of a specific unique key that exists in the data but
which hasn't been declared? If so, it's almost certainly worth your
while to create it. If not, then obviously there's some undetermined
performance benefit to be gained from removing the redundant data. In
that case you would have to check to see how big a job the cleanup is
and whether some code would need remediation to prevent key violations
in future. Probably that's no small task but past experience suggests
it's always worthwhile in the long term.

I might have confused you.

We do have plenty of rows in NOTICES on the same OBJNR, but the data isn't
redundant. Just like a ORDERLINES table referencing a ORDERS table. One
record in MAIN can have zero, one, or more records in NOTICES. These records
are different, but I have no unique index on them.

Was this clarifying?
Show quoteHide quote

So you're saying that in theory the compound key consists of objnr and
some other column(s)? In that case are you aware of a reason why it
isn't declared? As I suggested before, you ought to have a good reason
NOT to declare a key.

I notice that you don't have a clustered index either. That's not
necessarily a mistake but most tables should have a clustered index.

I cannot declare a composite key, unless I do it on a combination of all
columns. The data I get during updates are generated from the RMS running on
Alpha, and it won't ever be redundant, but doesn't have a unique key either.

I still haven't seen any reason for me to add the PK to my tables. As I
mentioned, this is a system ported from RMS, done by someone other than
myself (the client), and I need a good reason to start adding PK's to all
tables.

Regarding indexes: We've done some index tuning, but we're not done yet.
We'll be continuing down that road when we get the data and applications to
the staging server where we can apply a close-to-real-life load to the
system, do a trace, and run the index tuning wizard with that trace file as
input.

In that case you have a problem because 3 of those columns are nullable
therefore your table lacks a relational key at all. As you've described it
this is a kludge around a legacy system. I would recommend a proper design
review with the goal of getting this to 3NF as a start. After all, the fact
that they've ported it without properly reviewing the logical model doesn't
inspire much confidence in the rest of the design.

Technically SQL Server's unique indexes support NULLs as "real" values - in
violation of SQL Standards and the Relational model - so it is possible to
support a compromised design such as this. If you're only looking for
short-term performance gains then I would just experiment with the best
indexes based on your Profiler trace. Unless the client accepts the redesign
as necessary you don't have much option.

Problems with identity column
Problems with an INSERT SP
Date query
EM can't connect to server
database size
Outer Joins
Select best data from a group of related records
Save Table Definitions to Text File?
add column to exiting table
T-SQL Question