CODEWITHSUNDEEP
sql-serverperformanceindexing
Liath
Liath

Reputation: 10191

How much of a performance impact does the data type have on the PK?

I have worked on a number of systems which use different types as their PK. Common types are:

I'm aware that the more memory used in the field the larger the indicies become and so the slower searches (so clearly an nvarchar(1024) would be very bad!)

How dramatic (if any) are the performance changes when using different data types for PK columns?

Upvotes: 0

Views: 126

Answers (1)

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3750

Choosing a combination of several varchar columns as a primary key it becomes large. SQL Server automatically adds a clustered index on a primary key, if you already don't have one and an index also becomes big (much bigger on varchar than on an integer column) which results with increased number of index pages used to store the index keys. This increases the number of reads required to read the index and degrades overall index performance. Integers require less bytes to store, hence the primary key index structure will be smaller. Also, a primary key column(s) with varchar may make the JOIN statements slower as compared to the integer joins.

uniqueidentifier is also useful if you need GUID keys - guaranteed to be unique across all tables in your schema, databases, servers. But they are 4 times larger than the traditional 4-byte index value in integer and this may have performance and storage downsides

You can also see more info of a cost of GUID vs int here

And here Selecting the right datatype to improve database performance

Hope this helps

Upvotes: 2

Related Questions