How to store daily records in SQL Server 2017 database? [closed]

Posted on

Question :

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries.


enter image description here

and so on…

Now, as the application will grow, the nummber of users will also increase, thus, making millions of records. I have to fetch monthly, yearly quarterly data from this table, which will take too much time to fetch whatever query I will use. So, I want to ask what can I do to speed up the process of fetching the data fastly?
Also, can anyone please tell me what better design can be given to this table?

NOTE: I am a beginner in Database work and has very little and limited knowledge.

Answer :

Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.

You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.

You can try with monthly partition table design using date column. It will keep each month’s data in appropriate data files and easy to manage. Index B-Tree structure would faster in this method with big volume of data.

  • Index searching page count will be less and faster as per B-Tree structure

  • Easy to make read-only, remove specific files from database

  • Can take backup for individual files/file groups in huge database like partial backup.

Leave a Reply

Your email address will not be published. Required fields are marked *