Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
    
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 '12 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 –  tunmise fasipe Aug 23 '12 at 8:38
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
now I know better. I underestimated that value (Int32). Billion is not a small number –  tunmise fasipe Aug 23 '12 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! –  Cyril Gandon Aug 23 '12 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 –  Emmad Kareem Aug 23 '12 at 9:41
add comment

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?

share|improve this answer
    
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 '12 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! –  GlenPeterson Aug 23 '12 at 14:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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