CODEWITHSUNDEEP
sql-serversql-server-2008
John
John

Reputation: 347

Binary datatype for Primary Keys Vs Int?

Are there performance (or other) issues using the Binary datatype for Primary Keys. The database has a large number of large tables that are regularly joined using these keys. The indexes are clustered. I believe that these can't be automatically incremented (as an Identity field).

Upvotes: 10

Views: 6241

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11773

In SQL Server the Primary Key is by default also the key for the clustered index.

The Primary Key itself only needs to be unique and not nullable. There are no other restrictions.

The clustered index key however should be as short as possible. In most cases an ever increasing value is also preferred. The reason is that an index's depth is directly affected by the length of the index key. That is true for any index type. The clustered index key however gets automatically appended to each other index key on that table therefore multiplying the negative effect of a long key. That means in most cases an INT IDENTITY is a good choice.

If your Primary Key is non-clustered keeping it short is not that important. However, you are using it for joins. That means you probably have an index on this key on each child table too, therefore multiplying the problem again. So again, a automatically increasing surrogate key is probably the better choice.

This all is true for many if not most cases. However, there are always exceptions. You do not give a lot of information about your use case so the answer has to be general in nature. Make sure you test the performance of read as well as modification operations in your environment with realistic data before deciding which way to go.

As a final remark, a 4 byte BINARY and an INT are probably very close in performance. A difference you might see if the values are not created in a increasing binary-sorted way. That can cause page splits during insert operations and therefore impact your write performance.

Upvotes: 11

Related Questions