Is Having Primary key on a table essential?

Question

Is Having Primary key on a table essential?

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
May 2, 2013 at 7:41 pm

#273548

Hi everyone
We are using Sql Server 2008 and we have a log table which has one clustered index(on creationdate, that can be duplicate) and two non clustered index.This table is being used for logging and being inserted and updated frequently.Is it neccessary to create a primary key(new identity column) on this table?and if we do not create any,will that cause any problem?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Aspet Golestanian Namagerdi (5/2/2013)
Hi everyone
We are using Sql Server 2008 and we have a log table which has one clustered index(on creationdate, that can be duplicate) and two non clustered index.This table is being used for logging and being inserted and updated frequently.Is it neccessary to create a primary key(new identity column) on this table?and if we do not create any,will that cause any problem?
Thanks

No, it's not essential to have a PK but very beneficial. And for a log table, it should be on the CreationDate and an IDENTITY column AND it should be the clustered index. That way the table has very little chance of becoming fragmented and your clustered index will be UNIQUE which will cause great benefit for any index because the clustered index becomes a part of every non-clustered index.

As a sidebar, you're saying this table is being usef for logging. Why then would this table ever be made to suffer and UPDATE? Logs are logs and should never see an UPDATE ever.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 2

If you want a relational solution, a primary key is essential. If a non-relational solution will do, it isn't. Of course you have to care about whether a relational solution will give you any advantages of a non-relational one, and with MS-SQL Server the case of a logging table where you sometimes look up entries based on something other than the log time a relational solution has significant advantages, as Jeff pointed out, so you ought to have one.

Tom

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 3

Just remember that if you have a clustered index that is not unique, then SQL Server makes it unique through an internal process called a uniquifier. It's additional storage and maintenance overhead. Since you're saying this is a high volume part of your system, having that non-unique cluster might be an issue. Just something else to consider.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Grant Fritchey (5/6/2013)
Just remember that if you have a clustered index that is not unique, then SQL Server makes it unique through an internal process called a uniquifier. It's additional storage and maintenance overhead. Since you're saying this is a high volume part of your system, having that non-unique cluster might be an issue. Just something else to consider.

+1. Good to see emphasis on this. That's also why I said (in my previous post)...

and your clustered index will be UNIQUE which will cause great benefit...

... although I didn't explain why like Grant did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

Jeff Moden (5/6/2013)
Grant Fritchey (5/6/2013)
Just remember that if you have a clustered index that is not unique, then SQL Server makes it unique through an internal process called a uniquifier. It's additional storage and maintenance overhead. Since you're saying this is a high volume part of your system, having that non-unique cluster might be an issue. Just something else to consider.
+1. Good to see emphasis on this. That's also why I said (in my previous post)...
and your clustered index will be UNIQUE which will cause great benefit...
... although I didn't explain why like Grant did.

Yeah, I wasn't try to suggest you were off, just adding to the overall picture, hopefully.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Aspet Golestanian Namagerdi (5/2/2013)
We are using Sql Server 2008 and we have a log table which has one clustered index(on creationdate, that can be duplicate) and two non clustered index.This table is being used for logging and being inserted and updated frequently.Is it neccessary to create a primary key(new identity column) on this table?and if we do not create any,will that cause any problem?

Is this "log" table actually updated? That is a bit unusual for a pure "log" table.

If it was a pure log table e.g. insert-stuff-that-happens-in-it I would say you can live with no PK but, if the table is actually updated as stated in the post better if you have a way to identify every single row on it, this means the ability to uniquely identify a particular row which most probably will lead to the creation of an unique index or a proper PK.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.