SQL Server Blog

Temporal Tables (also known as system-versioned temporal tables) are essentially tables which return data based on a specific point in time.

These are ideal for use with slowly changing dimensions in data warehouses, or simply if you wish to easily and simply keep a queryable history of your data for audit or reporting purposes.

As this is a new (well, since SQL 2016) technology, there is a new syntax to remember. Luckily it’s not that complicated as it’s been built into the standard CREATE TABLE statement.

Finally I think I’m done with the topic of Slowly Changing Dimensions for the moment. This doesn’t mean this is all you need to know about them, or that I’ve covered every angle and consideration (I haven’t), but this should now suffice for most people to make an informed and considered decision as to when to use them, how to use them, and how best to populate them.

In the previous post I went through what a Slowly Changing Dimension is and why they can be such a problem for people when designing database solutions. Here I’ll start to work through a few implementations. Note there is no real “this is the way to go” solution because each has its advantages and pitfalls and needs to be considered carefully by each architect for the design at hand but, hopefully, knowing a few solutions will mean that you can try and few and find one that you’re most comfortable with.

In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

In all databases, data mart architects and developers have inevitably been faced with the one peril we all wish was easier… Slowly Changing Dimensions.

If you’ve heard the term and know all about the frustration they can cause then please skip ahead to my next blog post about Temporal Tables which in most, but definitely not all, scenarios can be a mini saviour. However, if you aren’t aware of Slowly Changing Dimensions or aren’t sure what they are, then read on…

One of the most common requirements within websites and other application is the ability to page results. This used to be an onerous task and, in many cases I’ve seen, people have tended to simply pull a full dataset from SQL Server, cache the results in the application, and then let the application itself page out the results accordingly. However, there is an easier way to do this directly from within SQL Server without using complex code.

Let’s say we’re trying to page out the Sales Orders from AdventureWorks by SalesOrderID. An older way of doing this might be as follows: