I am designing a new SQL Database for my ASP.net web application and I can foresee that some of the ID columns will get very large in the feature need INT and BIGINT but that will not be for a few years. With Entity Framework in ASP.net it is fairly easy to change column data types. So I was wondering, from a design point and performance point, is it better to start off using the smaller column types like TINYINT and SMALLINT and grow to INT and BIGINT when the time finally comes?
Is it better to start with a smaller INT types and grow as needed
database-design
Related Question
- Sql-server – Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type
- Help with designing a multi relational schema / domain model
- Sql-server – Options for structuring multiple associations to a record
- Sql-server – DB Design Challenge
- Normalizing the Test Table
- Best practices for integer key types – mixed or all the same integer type
Best Answer
I would suggest right-sizing your columns from the get-go. You need to take into account expected growth of the data versus the life of the application. If it will take 100 years for your integer data to require an INT datatype, then you can likely stick with a SMALLINT if you think your application likely won't live that long. If it will take only a few years to get there, then it would be easier to just handle that right now rather than risk an application failure when the upper limit of SMALLINT is reached or to risk forgetting to fix it when it gets close.
If you don't, your application will likely fail at some point, likely in the middle of the night or when you're least expecting it! Or will fail for someone else who is tasked with maintaining your application.
If the users can live with having your application be down while you determine that your data type has exceeded it's size and take steps to fix it, then you can wait. I would bet that is not an acceptable scenario though and you would like to keep the application up time as close to 100% as possible.
Don't wait for "someday" to arrive, get it right today!