Share via

Optimal Table design

AC 1 Reputation point
2020-10-06T23:57:02.04+00:00

I am looking for some suggestion about how to make design a table in Azure SQL Database so that it will perform fast.

The table has few foreign keys, and a composite key which is a clustered index. This table will be updated ( data will be inserted) every day, and this table will have billions of records over time. The operation of the application accessing this table is only selectivity and does not have insert/delete/updates.

I already have below items in mind to implement:

  • Table partitioning
  • Proper indexing
  • Archiving old data ( older data that is not needed for the application)

Inserts will happen once a day with new data to be loaded every day.

I would appreciate any valuable suggestions on this regard.

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KalyanChanumolu-MSFT 8,361 Reputation points
    2020-10-08T11:40:42.287+00:00

    @AC Thank you for reaching out.

    Looks like you already thought this through very well.
    You should also rebuild the indexes after data is loaded/archived.

    Azure SQL Database can automatically analyze your access patterns and provide recommendations to enhance performance.

    Please post here and let us know if you have any specific issues after implementation.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.