Do I need ID field?

I have an object which was identified by a GUID. I will store this in SQL DB and designing the table. Should I use guid as the PK 开发者_开发问答or should I create an aditional ID field ? I will never use ID field I know . What is the benefit in this case ? I saw it was created what might be their motivation ?

---

Whether you also need a surrogate key depends on your particular needs. GUIDS can be slower for joining, but only you can know if they will affect the performance of your system enough to warrant adding an int key. There are also issues with GUIDS and how the data is physically stored on the disk and the performance issues that causes. Additonally, since PKs are in all other indexes, using a GUID for a PK can greatly increase the size of your index. Before commmitting to use GUIDs, you need to do some reading on the performance implications (which may be differnt from database to database).

GUIDS are not user friendly. If you don't have a good natural key in a table (sucha as person table where names are not unique and thus are not able to be a natural key) then users may need to have some other value to query on (such as person_id). THe average employee is going to be happier researching customer 1234 than customer '6214304C-2C56-E011-BACB-00265582C0F2' Nor do I see customers as wanting to call up customer service to ask for help with order number '6E14304C-2C56-E011-BACB-00265582C0F2'. This is not an unimportant consideration.

I'm not saying you can't use the GUID (you certainly can), but you need to know before you commit to the path exactly what kinds of issues you might have with using the GUID. Databasesa re not easy to change after there are millions of records, espcially with something as critical to the design as the PK. Many of the problems with GUIDs aren't particularly noticable until you have lots of records. If the database is one that will never have that level of records, you may never run into the issue, but you need to think about the implications in terms of your own expected database use before you decide.

---

A table should have at least one key but there's no reason to create another one if you aren't going to need it.

---

One possible case is when you have records coming in from another database/system which has it's own GUIDs.

---

To understand the motivation we'd need to understand the application.

Reasons not to have one: Guid is all you'll need to access, serach, retrieve, blah the record

Reasons to have one: Information is virtualized within the data. Meaning that guid while sufficient for a DB point of view, might not be for a user point of view. the additionnal ID field could be a primary field when considered in terms of a business process, and the content of that field used in other systems

---

It's difficult to answer authoritatively without knowing more about your business requirements. It certainly might be legitimate to use a natural primary key. Expand on your requirements here, or do some reading on natural and synthetic primary keys.