The limit of Int32 for Identity Column

Question

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

Answer 1

Int32 is a 32 bit integer. It's also signed. This means it can hold 2^³¹ - 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^⁶³ - 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.

Answer 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?