CODEWITHSUNDEEP
sqlsql-server
wootscootinboogie
wootscootinboogie

Reputation: 8705

Does primary key datatype affect performance? SQL Server 2008

Is there a performance hit if you use one data type for your primary key versus another?

I have a large database that will be queried often, but only written to once a year. Will it matter at all if I supply an incremental primary key (integer) or let SQL Server give it a GUID?

Upvotes: 4

Views: 1239

Answers (1)

Oded
Oded

Reputation: 499352

There is a performance hit, in particular with large amounts of data.

In general, use the smallest data type that will fit your needs (you can always use a larger one if needed).

When it comes to INT(4) versus UNIQUIDENTIFIER, the former is much smaller, takes less disk space and less bytes go over the wire. This also means that indexes using such a PK will be smaller. Of course, all of this data also needs to fit in memory, so the more you can fit into memory, the better.

Upvotes: 8

Related Questions