After my post about MySQL and the the reason for going that route being the size limit of MS SQL Server Express 2005, I started thinking how big is 4GB?
Will it really be a limitation for the application we’re planning to develop? So, I’ve designed a little experiment to determine just how much data can fit into a 4GB MS SQL database. I wanted an easy way to quantify the amount of data, just populating it with a bunch of random values would not serve the purpose. With this in mind, I designed an app that writes date values to a table. You can specify the range using datepickers and you can set the interval for each record i.e. each day, month or year.
The table design looked like this:
I deliberately made it nice large fields.The table size without any data looked like this(using sp_spaceused):
First thing was to get the size of just one record, which looked like this:
Afterwards I add a entry to the table for each day for a thousand years(This scenario could be used to test transaction history sizes). The size after that looked like this :
Not bad…1000 Years of data and we’re only at about 38MB…now, my aim was to get the tablesize to 1GB to give an indication of the amount of data we’re looking at and at this rate things we’re looking good with regards if whether we would be able to use SQL Express.
Now to get 1GB of data in the table, I did a quick computation. If 1000 years = 38MB then 26000 years = 1GB. That’s a whole lot of years. Remember we’re adding one entry for every day for 26000 years. That’s a whole-lotta-transactions!
But OK, I doubt we’re going to use this application for a 1000years, so I wanted to get a more real life scenario. So I’ve added a 1000 entries for every day for 5 years. That should be a pretty good scenario for order movements or transactions over 5 years.
Unbelievably the data is not that large at all:
With this information, we’ve decided to stick with SQL Express. 4GB should be more than enough!