CODEWITHSUNDEEP
sqldatabase-designprimary-keyidentityuniqueidentifier
Punit
Punit

Reputation: 1445

is it good to have primary keys as Identity field

I have read a lot of articles about whether we should have primary keys that are identity columns, but I'm still confused.

There are advantages of making columns are identity as it would give better performance in joins and provides data consistency. But there is a major drawback associated with identity ,i.e.When INSERT statement fails, still the IDENTITY value increases If a transaction is rolled back, the new IDENTITY column value isn't rolled back, so we end up with gaps in sequencing. I can use GUIDs (by using NEWSEQUENTIALID) but it reduces performance.

Upvotes: 3

Views: 1528

Answers (4)

Erwin Smout
Erwin Smout

Reputation: 18408

"yes, it KILLS performance - totally. I went from a legacy system with GUID as PK/CK and 99.5% index fragmentation on a daily basis to using INT IDENTITY - HUGE difference. Hardly any index fragmentation anymore, performance is significantly better. GUIDs as Clustering Index on your SQL Server table are BAD BAD BAD - period."

Might be true, but I see no logical reasoning according to which this leads me to conclude that GUIDs PER SE are also BAD BAD BAD.

Maybe you should consider using other types of indexes on such data. And if your dbms does not offer you a choice between several types of index, then perhaps you should consider getting yourself a better dbms.

Upvotes: 0

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

For all practical purposes, integers are ideal for primary keys and auto increment is a perfect way to generate them. As long as your PK is meaningless (surrogate) it will be protected from creativity of you customers and serve its main purpose (to identify a row in a table) just fine. Indexes are packed, joins fast as it gets, and it is easy to partition tables.
If you happen to need GUID, that's fine too; however, think auto-increment integer first.

Upvotes: 4

Jan
Jan

Reputation: 16032

I would like to say that depends on your needs. We use only Guids as primary keys (with default set to NewID) because we develop a distributed system with many Sql Server instances, so we have to be sure that every Sql Server generate unique primary key values. But when using a Guid column as PK, be sure not to use it as your clustered index (thanks to marc_s for the link)

Advantage of the Guid type:

  • You can create unique values on different locations without synchronization

Disadvantage:

  • Its a large datatype (16 Bytes) and needs significant more space
  • It creates index fragmentation (at least when using the newid() function)

Dataconsistency is not an issue with primary keys independent of the datatype because a primary key has to be unique by definition!

I don't believe that an identity column has better join performance. At all, performance is a matter of the right indexes. A primary key is a constraint not an index.

Is your need to have a primary key of typ int with no gaps? This should'nt be a problem normally.

Upvotes: 1

gbn
gbn

Reputation: 432200

Gaps should not matter: the identity column is internal and not for end user usage or recognition.

GUIDs will kill performance, even sequential ones, because of the 16 byte width.

An identity column should be chosen to respect the physical implementation after modelling your data and working out what your natural keys are. That is, the chosen natural key is the logical key but you choose a surrogate key (identity) because you know how the engine works.

Or you use an ORM and let the client tail wag the database dog...

Upvotes: 8

Related Questions