Menu
I know some software shops have been burned by using the int type for the primary key of a persistent class. That being said, not all tables grow past 2 billions. As a matter of fact, most don't.
So, do you guys use the long type only for those classes that are mapped to potentially large tables OR for every persistent class just to be consistent? What's the industry concensus?
I'll leave this question open for a while so that you can share with us your success/horror stories.
601
Accepted Answer It depends on how many records will be in a table. Int allows only 2*10^9 records per table. If you are sure, that 2*10^9 is enough, use int as a key. But: If there is a tiny chance that count of records will be more than 2*10^9, use the long.
You could change primary key to bigint, bigint (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
No, the primary key does not have to be an integer; it's just very common that it is. As an example, we have User ID's here that can have leading zeroes and so must be stored in a varchar field. That field is used as a primary key in our Employee table.
Integer (number) data types are the best choice for primary key, followed by fixed-length character data types. SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.
Long can be advantageous even if the table does not grow super large, yet has a high turnover ie if rows are deleted/inserted frequently. Your auto-generated/sequential unique identifier may increment to a high number while the table remains small.
I generally use Long because the performance benefits are not noticeable in most of my projects, however a bug due to overflow would be very noticeable!
That's not to say that Int is not a better option for other people's scenarios, for example for data crunching or complex query systems. Just be clear of the risks/benefits and how they impact your specific project.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
