SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is Having Primary key on a table essential?


Is Having Primary key on a table essential?

Author
Message
Aspet Golestanian Namagerdi
Aspet Golestanian Namagerdi
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 863
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216238 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26055 Visits: 12499
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
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98833 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216238 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98833 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14243 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search