SQL primary key, INT or GUID or..? [duplicate]

This question already has answers here: INT vs Unique-Identifier for ID field in database 开发者_如何学运维 (6 answers) Closed 9 years ago.

Is there any reason why I should not use an Integer as primary key for my tables?

Database is SQL-CE, two main tables of approx 50,000 entries per year, and a few minor tables. Only two connections will exist constantly open to the database. But updates will be triggered through multiple TCP socket connections, so it will be many cross threads that access and use the same database-connection. Although activity is very low, so simultanous updates are quite unlikely, but may occur maybe a couple of times per day max.

Will probably use LINQ2SQL for DAL, or typed datasets.

Not sure if this info is relevant, but that's why I'm asking, since I don't know :)

---

You should use an integer - it is smaller, meaning less memory, less IO (disk and network), less work to join on.

The database should handle the concurrency issues, regardless of the type of PK.

---

The advantage of using GUID primkey is that it should be unique in the world, such as whether to move data from one database to another. So you know that the row is unique.

But if we are talking about a small db, so I prefer integer.

Edit:

If you using SQL Server 2005++, can you also use NEWSEQUENTIALID(), this generates a GUID based on the row above.Allows the index problem with newid() is not there anymore.

---

I see no reason not to use an auto-increment integer in this scenario. If you ever get to the point where an integer can't handle the volume of data then you're talking about an application scaled up to the point that a lot more work is involved anyway.

Keep in mind a few things:

1. An integer is the native word size for the hardware. It's about as fast and simple and easy on the computer as a data type gets.
2. When considering possibly using a GUID, know that they make for terrible primary keys. Relational databases in general (I can't speak for all, but MS SQL is a good example) don't index GUIDs well. There are hacks out there to try to make more index-friendly GUIDs, take them or leave them. But in general a GUID should be avoided as a PK for performance reasons.

---

Is there any reason why I should not use an Integer as primary key for my tables?

Nope, as long as each one is unique, integers are fine. Guids sounds like a good idea at first, but in reality they are much too large. Most of the time, it's using a sledgehammer to kill a fly, and the size of the Guid makes it much slower than using an integer.

---

Definitely use an integer, you do not want to use a GUID in a clustered index (PK) as it will cause the table to unnecessarily fragment.