Performance: Primary Key = int/nvarchar/guid/...

17 Mar 2006 8:19 AM

Pieter

Hi,

Is there a difference in performance when the primary keys of the tables
are:
- int
- int with Identity
- nvarchar
- uniqueidentifier
?

And if yes: which?

Thanks a lot in advance,

Pieter

17 Mar 2006 8:33 AM

Uri Dimant

Pieter , it is really open ended question. On primary keys you can create
nonclustered index as well as clustered
There are diferences interm of perfomance.I suggets you to read an article
about constraints in the BOL

http://www.sql-server-performance.com/clustered_indexes.asp

Show quoteHide quote

"Pieter" <pietercou***@hotmail.com> wrote in message
news:uEyWJuZSGHA.5468@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> Is there a difference in performance when the primary keys of the tables
> are:
> - int
> - int with Identity
> - nvarchar
> - uniqueidentifier
> ?
>
> And if yes: which?
>
> Thanks a lot in advance,
>
> Pieter
>

17 Mar 2006 11:15 AM

SQLpro [MVP]

Pieter a écrit :
Show quoteHide quote

> Hi,
>
> Is there a difference in performance when the primary keys of the tables
> are:
> - int
> - int with Identity
> - nvarchar
> - uniqueidentifier
> ?
>
> And if yes: which?
>
> Thanks a lot in advance,
>
> Pieter
>
>

in terms of perf, an integer based on the size of the CPU word is always
the best choice.

if you are under 32 bits CPU, choose INT
BIGINT or SMALLINT will be slower
CHAR(4) OR NCHAR(2) wich have the same size (32 bits) will also be
smaller due to conversion for collating datas.

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

17 Mar 2006 2:52 PM

David Browne

Show quote Hide quote

"SQLpro [MVP]" <broua***@club-internet.fr> wrote in message
news:%23emroQbSGHA.4740@TK2MSFTNGP14.phx.gbl...
> Pieter a écrit :
>> Hi,
>>
>> Is there a difference in performance when the primary keys of the tables
>> are:
>> - int
>> - int with Identity
>> - nvarchar
>> - uniqueidentifier
>> ?
>>
>> And if yes: which?
>>
>> Thanks a lot in advance,
>>
>> Pieter
>
> in terms of perf, an integer based on the size of the CPU word is always
> the best choice.
>

I don't think you can make that generalization. Smaller types have a
performance advantage because you can fit more entries (rows, keys) on a
page. This will reduce the amount of logical IO required to use the object,
and should be more pronounced than any affect of using the native CPU size.

In short I suspect INT will outperform LONG on every platform.

David

17 Mar 2006 3:45 PM

Pieter

Thanks guys.

And will the performance-difference be notisable?

I'm thinking especially of tables that contains 10000 records, and some
SELECT-query's must be used. There won't be really a lot of inserts or
updates: max 100 a day?

Pieter

17 Mar 2006 6:00 PM

David Browne

"Pieter" <pietercou***@hotmail.com> wrote in message
news:uLkPRndSGHA.5156@TK2MSFTNGP10.phx.gbl...
> Thanks guys.
>
> And will the performance-difference be notisable?
>
> I'm thinking especially of tables that contains 10000 records, and some
> SELECT-query's must be used. There won't be really a lot of inserts or
> updates: max 100 a day?
>

With this small number of rows, probably not notisable.

David