Should I use int32 or int64 for the primary keys in my SQL Server database?
sql-server
Solution
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.
Problem
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.