The schema of table is :


PS: two or more patient can came at the same time . The number of appointments per day up to 500 .

Which is better for performance, using auto-incremented primary key or a composite primary key (date,time,pateintID)?

  • What database platform? If SQL Server, Kimberly Tripp has a great explanation of why using an auto-inc is a great choice here: sqlskills.com/blogs/kimberly/… – mskinner Oct 24 '14 at 19:11
  • With 500 rows per day, it doesn't really matter. – ypercubeᵀᴹ Oct 24 '14 at 19:39
  • Do you expect to query the appointment table by the artificial key value? If so, a single auto-increment key lookup might be marginally faster than the composite key lookup, which you probably won't notice anyway unless you're running a TPC-C benchmark. – mustaccio Oct 24 '14 at 19:41

From a purely performance standpoint, using the identity key as a clustered primary key will be most efficient. From there I would do a non clustered key with a unique constraint on datetime, patientid.

  • I guess you assume that the primary key is the clustered one. – ypercubeᵀᴹ Oct 24 '14 at 20:24
  • From a performance standpoint, yes I did. There would be very little overhead in the B-Tree and the leaf level, by keeping the key small and fixed. – Steve Mangiameli Oct 24 '14 at 20:30
  • I meant that you should edit the "the identity key will be most efficient" to something like "to use the identity key as the clustered key will be most efficient" – ypercubeᵀᴹ Oct 24 '14 at 20:47
  • Gotcha. Editted. Sometimes my hands assume everyone else can read what's in my head. – Steve Mangiameli Oct 24 '14 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.