Should I use int32 or int64 for the primary keys in my SQL Server database?

Question

When creating a new database in SQL Server, should I use primary keys as int32 or int64?

Is the int32 faster?

I think the reason that I would not want to go with int32 is that: wouldn't the upper limit on the int32 datatype limit the total transactions on a particular table to 2,147,483,647? I have no intention of storing that much data in there, but what if I constantly needed to create new records and then delete others that were stale? many transactions later, wouldn't I run out of keys? Is there some method that takes care of this already? Or should I just use the long data type.

Answer 1

With a type INT (int32), starting at 1, you get over 2 billion possible rows - that should be more than sufficient for the vast majority of cases. With BIGINT (int64), you get roughly 9 quintillion (9 with 18 zeros - 9'223'000 billions) - enough for you??

Just a few quick calculations:

• If you use an INT IDENTITY starting at 1, and you insert a row every second, you need 66.5 years before you hit the 2 billion limit ....

• If you use a BIGINT IDENTITY starting at 1, and you insert one thousand rows every second, you need a mind-boggling 292 million years before you hit the 9.22 quintillion limit ....

Read more about it (with all the options there are) in the MSDN Books Online.