0

This is just a consideration for a site am creating and for other big sites out there.

I am using Identity Column to store the ID of some of my tables and I have classes whose Id are decorated with Int32 to hold the value of the ID retrieved from database.

My worry is that as the site grows bigger, some tables that grows exponentially e.g QuestionComments might exceed the Int32 limit in future. So I change my class to use long.

public class Question
{
   public long QuestionID { get; set; }
   ...
}

//Converting database value to .Net type

 Question q = new Question();
 q.QuestionID = Convert.ToInt32(myDataRow["QuestionID"]);

How true is my assumption? Would using a UniqueIdentifier be better? Are there other way to address this?

UPDATE:

But for the sake of learning, how would site like FaceBook, Google, StackOverflow etc. handle Visit table assuming they have VisitID as Identity Column

2
  • what field are you using to store the data in the database? use the one that most closely matches it. do you really think 2 billion is a small enough number to cause issues? for instance, this site has been going for years and features only 20k questions. SO has only ~3.5 million.
    – TZHX
    Aug 23, 2012 at 8:30
  • now I know better. I underestimated that value (Int32). If I go by YAGNI, then this question is already answered. But for the sake of learning, how would site like FaceBook, Google, StackOverflow etc. handle Visit table assuming they have VisitID as Identity Column Aug 23, 2012 at 8:38

2 Answers 2

6

Int32 is a 32 bit integer. It's also signed. This means it can hold 2^31 - 1 different positive values. This comes to:

2,147,483,647

That's over 2 billion unique IDs. If you can use an unsigned integer this doubles to 4 billion.

long is a 64 bit signed integer. That's 2^63 - 1 different positive values which comes to:

9,223,372,036,854,775,807

That's over 9 quintillion unique IDs (name courtesy of http://www.webmath.com) which is many orders of magnitude more than the 32 bit integer can hold.

So if you think you are going to get over 2 billion entities in any of your tables you should use long. This should be enough for any purpose.

3
  • now I know better. I underestimated that value (Int32). Billion is not a small number Aug 23, 2012 at 8:41
  • 1
    If I may add too, 2 billions is not that big, and if ever you reach this number, this will be nasty. My personal choice is always to use 64 bits int for identity. That's not an effort, this is just a choice to make. That doesn't make your life worse and you fell better! Aug 23, 2012 at 9:34
  • 1
    If I may add, make sure that the types you use in your code maps exactly to the data types on your table definition please see: msdn.microsoft.com/en-us/library/cc716729.aspx
    – NoChance
    Aug 23, 2012 at 9:41
2

I was under the impression that any modern database used a 64-bit unsigned "long" integer as a surrogate key (unique identifier) by default. In MySQL, every record has a row number and if you declare this column properly, it does not actually take any (additional) storage in the database. Why would you ever use anything else?

2
  • on a 64-bit server this is true, on a 32-bit one however, you're doing a lot more processing of those 64-bit numbers.
    – gbjbaanb
    Aug 23, 2012 at 13:24
  • Can you provide links to some examples? Also, how big of a processing hit are we talking about? I don't think any math is being performed with unique IDs. 64-bit keys on a 32-bit system would take 2 retrievals and 2 comparisons instead of 1. But it's still probably a lot faster than using strings, especially if character encoding is involved. Hey, if 32-bits is enough, then that's great! Aug 23, 2012 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.