0

I am busy designing a log table which certain entries will be stored in, I have simplified the problem for this question.

Here is an example of a standard table:

bigInt: PK | int:client_id | datetime: time | string:data

Now the way I see it, the PK in this case is not really needed in terms of me querying the data which I need? Is there a way you could create a virtual / compound primary key?

So in the case above, you could identity this row, buy a compound PK of client_id and time. This way you would save on having to store a useless integer for every entry?

Or an I missing something important about the primary key?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zapnologica
  • 22,170
  • 44
  • 158
  • 253
  • easy search gives many results: http://stackoverflow.com/a/1110364/1692632 – Darka May 02 '14 at 05:47
  • The primary key in SQL Server is by default also the **clustering key**, which is duplicated into all nonclustered indices on that table. Therefore, the clsutering key should be unique and narrow - using multiple columns as your PK usually tends to be less ideal. I've seen many cases where **adding** an *useless integer* actually makes the whole table **SMALLER** in the end (table data including the index sizes) as compared to using a bloated, compound primary key – marc_s May 02 '14 at 07:23
  • So you saying the 8bytes the pk uses wont hard the table that much, as there are other benefits to having it? – Zapnologica May 02 '14 at 09:40

1 Answers1

1

Yes, it's definitely possible to use a compound primary key in SQL server.

PK, on the other hand, is what's called a surrogate key: A key field which is not strictly necessary, but which provides easy access to a row.

More importantly, a surrogate key never changes: client_id and time (the natural key), have some business meaning, and, thus, might need to be modified, which can be a hassle when other tables reference them as a foreign key.

The Wikipedia article on Surrogate Key provides a lot of information on the pros and cons of both approaches.

Heinzi
  • 167,459
  • 57
  • 363
  • 519