CODEWITHSUNDEEP
sqlsql-serversql-server-2008loggingprimary-key
Phistrom
Phistrom

Reputation: 581

Primary keys in a logging database

I'm creating a database that is going to be logging PC usage. There will be several entries of the format: Computer, User, AuditTime. It is for keeping track of who was logged in to what computer at the time an audit was performed. Computer and User reference tables of all the computers and users in our company and their respective departments.

Should I make Computer and AuditTime together a composite key? Is there a performance hit or gain from bothering to specify a primary key when really, a unique constraint on this table is unnecessary?

The database is MS SQL Server 2008.

Upvotes: 2

Views: 2185

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332661

Edit: Vote for Marc_s!

There is a performance hit by having a primary key -- that's why they aren't generally configured in tables with high insertion, low reading use (IE: logs). But a clustered key is good.

Upvotes: 3

marc_s
marc_s

Reputation: 754983

SQL Server makes your primary keys your clustering keys by default (unless you specifically tell it not to), and as Kimberly Tripp shows in her blog post The Clustered Index Debate Continues, having a clustered index on a table is beneficial for any operation - including INSERT and DELETE - as long as it's the right type of clustered index.

A good clustered index should be:

  • narrow
  • unique
  • stable (no changing)
  • ever increasing

The best fit would be a INT IDENTITY surrogate key - I would advise against compound indices for the vast majority of cases.

Upvotes: 4

Related Questions