2

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?

Improve this question
1
  • It can be a lot of work to modify an existing schema, especially if the columns are referenced. By the time you realize that you need a bigger column, you might not have time to complete the modification work before the IDs would saturate. You could move old data to an archive table, but it's a nightmare to reconcile. Be warned!
    – Iain Samuel McLean Elder
    Commented Jan 8, 2014 at 1:59

3 Answers 3

Reset to default
6

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!

Improve this answer
2

In general changing type of field can took some time - dependent on table size. Index also rebuilded in that case. On big tables with millions of records process can last a while. Penalties and overheads of bigints are not so significant to complicate things.

Improve this answer
1

I would suggest to use common sense, don't take it too far, and choose the right datatype that will satisfy your requirements for longer term.

  • If you're planning to start with tinyint => this suggests your table(s) will not increase so fast. Btw, do you know int in SQL Server means up to 2^31 (~ 2 billions rows) ?!

  • It's true: changing data types later can be difficult.

  • Also, a point to think at is that size matters for large tables, even just saying you're saving 4 bytes per row (=storage difference between int and bigint), you must think about scale:

    10^7 rows => 0.038 GB saved

    10^8 rows => 0.38 GB saved

    10^9 rows => 3.8 GB saved

Multiply this with x places where you might take the decision to use a bigint instead of int and see the results...

  • A clustered index widens nonclustered indexes
Improve this answer

Your Answer

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

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