28

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

Simon
  • 7,991
  • 21
  • 83
  • 163
  • 1
    Please note that if the primary key property of your Code First class is a `long` type, you may need to add the `[Key]` DataAnnotation to the property – Aaron Hoffman Jul 04 '15 at 16:59

3 Answers3

68

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Add 1000 to the counter for every SQL service shut down unexpectedly / failure because the server saves 1000 numbers ahead in the cache and doesn't place them back on failure so you can easily see jumps of 1000's in your identity columns. I would easily go for the 'long' (Int64) version in every identity column I define. In case you are not aware of this behavior: https://stackoverflow.com/a/14162761/665783 – Jacob Feb 26 '19 at 03:16
11

Both are OK. 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. If you don't have any idea how many records you'll have, use long.

Dmitriy Finozhenok
  • 844
  • 7
  • 8
6

Both C# and SQL have the same definition for an int, which is an integral data type that goes from -2^31 to 2^31 - 1 (2,147,483,647 if you prefer).

As you can see, your 100,000s of records could fit more than 20,000 times in an int, so yes, you will be alright in your current scenario.

A long however would translate into a bigint in sql (there's no "long" per se), and the value caps at 2^63 - 1.

Pierre-Luc Pineault
  • 8,993
  • 6
  • 40
  • 55