Menu
I am writing an MVC5 Internet application and I have a question about the id field for a model.
Should I use an int or a long for the id in a model? I am talking about the field that is used for the primary key? Is an int enough if the database has 100,000s of records? Is an int in c# different than an int in a SQL database?
Thanks in advance
629
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.
An INT is certainly much easier to read when debugging, and much smaller. I would, however, use a GUID or similar as a license key for a product. You know it's going to be unique, and you know that it's not going to be sequential.
Having a guid column is perfectly ok like any varchar column as long as you do not use it as PK part and in general as a key column to join tables. Your database must have its own PK elements, filtering and joining data using them - filtering also by a GUID afterwards is perfectly ok.
The Entity framework will not support to have a table without primary key, but we can overcome this issue by accessing the table with additional column via a view and marking the new column as Primary in entity framework. Entity Framework requires primary keys for entities.
With a type INT, starting at 1, you get over 2 billion possible rows - that should be more than sufficient for the vast majority of cases. With BIGINT, you get roughly 922 quadrillion (922 with 15 zeros - 922'000 billions) - enough for you??
If you use an INT IDENTITY starting at 1, and you insert one row every second, around the clock, you need 66.5 years before you hit the 2 billion limit ....
If you use a BIGINT IDENTITY (BIGINT in T-SQL is defined as long or Int64 in .NET languages) starting at 1, and you insert one thousand rows every second, you need a mind-boggling 292 million years before you hit the 922 quadrillion limit ....
Read more about it (with all the options there are) in the MSDN Books Online.
97
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
