Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is Having Primary key on a table essential? Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 7:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:46 PM
Points: 182, Visits: 737
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
Post #1449027
Posted Thursday, May 2, 2013 8:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449031
Posted Friday, May 3, 2013 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:21 PM
Points: 7,928, Visits: 9,653
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
Post #1449148
Posted Monday, May 6, 2013 5:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1449676
Posted Monday, May 6, 2013 9:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449752
Posted Monday, May 6, 2013 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1449757
Posted Monday, May 13, 2013 1:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1452291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse