Primary key design in SQL Server

Question

Right now, I am designing the database as you all know, primary keys are important factor in the database design.

I have dilemma in deciding the primary key between GUID, identity (Auto Increment) and a custom key for primary key, in which scenario should I use?

I am trying to database design for school. School has many branches in the different location and different cities. school have central database, they want to update every day at evening.

Please guide me? Thanks

yeah, but which factor need to care for decide above these ? — , Oct 10 '14 at 21:03
This question is pretty broad. You might consider telling more about your specific scenario otherwise an answer would have to include an impractically large amount of speculation. — pseudocoder, Oct 10 '14 at 21:03
Start with identity until you find a scenario where it doesn't work. — Laurence, Oct 10 '14 at 21:05
@RyanCalson I think this is a valid question I would check out SQL Skills Blog as they have a lot of good info (Especially Kimberly Tripps Blog) — bumble_bee_tuna, Oct 10 '14 at 23:07
@RyanCalson http://www.sqlskills.com/blogs/kimberly/category/clustering-key/ — bumble_bee_tuna, Oct 10 '14 at 23:08

score 2 · Accepted Answer · edited May 23 '17 at 12:22

In my opinion, the default choice is to use a 32-bit integer with auto-increment.

You might use GUID under specific scenarios that are covered in this QA: What are the best practices for using a GUID as a primary key, specifically regarding performance?

A custom key should only be used if you need to assign a specific key for some reason based on your design or based on business requirements.

Edit: Based on your update, if you are merging disconnected tables you might want to consider using GUID keys so you don't have problems with collisions.

score 0 · Answer 2 · answered Oct 10 '14 at 21:07

I suggest you to choose identity (Auto Increment) because when you use custom primary key you should generate an random number and then save it. when identity (Auto Increment) don't need. and when you use GUID sql or you have to generate a GUID and then store it. identity (Auto Increment) is more general and easy to understand too.

Primary key design in SQL Server

2 Answers2