I have recently been learning about temporal tables in AzureSQL and am considering implementing this into my codebase. However, columnstore indexes are only available for Azure SQL databases of tier S3 and above. The price of this tier is very high, so I am hesitant. How would temporal tables perform with a rowstore index instead of columnstore? Or is there any alternative tech, or a workaround? Or is the alternative to just create a DIY temporal table using triggers and an active flag on the most recent record?

Hope this makes sense and any feedback or ideas would be great :)

  • Why do you want to use Columnstore? Why do you want to use Temporal Tables? What sort of data size are we talking (row counts)? What is the expected churn rate of data in your tables? It's important to understand what problem you're hoping to solve by implementing these features, rather than implementing them just because.
    – HandyD
    Aug 24 '21 at 0:08

The performance will depend on what you try to do with them. If you're doing single-row lookups temporal tables (with appropriate indexes) will perform beautifully. If you're calculating aggregates over terabytes they'll be much worse. If you have many updates CCIs will suffer. There is no "best" solution, only ones with different sets of trade-offs.

From what I can see over a couple of implementations, temporal is exactly what you write if you were to roll-your-own and do it properly. There is no additional magic. It's just that the ugly plumbing is hidden behind some syntax sugar. (And likely it's been much better tested.)

When calculating the cost of an implementation remember to factor your development time, and future maintenance time, not just cloud service charges.

  • Thanks for your reply. I will be using the temporal tables for mainly table scans i.e reporting. I had not considered the cost of development time or future maintenance time so thanks for that :) Aug 23 '21 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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