Temporal tables in Azure SQL DB
simplify how you write
applications based on event
data or data that changes over time.
Davide Mauri is here to show us
how it works.
Today on Azure Friday
Hello again.
And today on Azure Friday
I have David Mauri,
program
manager from Azure SQL
to be on a continuing series
where we're talking about
all the great things
that we can do with Azure SQL DB.
Welcome, David.
How are you?
Hey, Lara. Good, good. Thanks.
Nice to hear here again.
Yeah.
So today
we want to
have a look at temporal tables.
He talked to us about why
these are important.
Yeah, you know,
that's a very interesting question
because we
are so used to use data
in its present state,
but sometimes we actually need to know
why data reached that state, right?
So we need to keep track
of all the changes
that brought data
from state A to B,
which is the present state.
And doing that is quite complex.
Now, lately, several design partners
kind of came up to kind of solve
this problem.
Securities
and even sourcing
as the most common and well known.
But they are quite complex in that.
First of all,
you may not be able to implement them
because you have an existing application.
Or secondly,
you don't want to tackle
all the complexity.
Maybe you just need to keep
track of changes.
That's why we have created
Temporal Table in Agile sequel,
and I just like to show it to you if you
if you like.
Oh, that'd be great.
I'd love to see this. Yeah.
So first of all, let me start to say
that we are creating a regular table,
but with three specific additional
option here, the validity.
So we want to say that we have a valid
from a valid tool
that says when Arrow was valid over time,
because otherwise
without this information
that we always represented
like the present state. Right?
And then we inform the system
that we want to use these two Coloma
to actually
kind of be using in this temporal ability
that we have.
So let me create this table.
Actually, I already did.
And I already enabled the system
version. Inga.
And then there is something
interesting here, Easter table.
So this means that every time
we change something in this table,
the row that has changed
that will be moved automatically for me.
No trigger too, right?
No sort of procedure,
no code at all into these Easter table.
Now, if that would be the only reason
to use temporal table,
you could say, well,
that's not a big deal, right?
Well, the problem starts
when we want to query the data
in the run,
what we call as off
query, like run the query
as if it was executed
like three days ago.
What data would I have seen
at that moment in time?
That's where the completely
a complexity arise.
So let me show you
first of all, let's start
to simulate that.
We have this table
and we want to insert a couple of orders.
So like I'm doing an order
and you are doing an order perfect.
Now, some time passes and
my order is completed.
Perfect.
Of course, other time passes
and let's say
that someone puts in another order
and then of course time passes again.
And then for some reason you decide
to cancel your order
and you're going to see
them say cancel
and actually
the system deletes your order.
Now, if we go in the current table,
we will see that
there are only two orders mine
with the latest updated version
and the other order made by Robert
that is in your is completely disappear.
But if I need to do some analysis,
I may need that information
like why your order is in there.
Maybe you call angry.
I haven't received my orders,
so we will see.
Let's see what happened
and then I can go in the history
and see exactly all the changes.
So at some point
someone, for example, in this case
updated my order and this is the moment
in which
that data was valid
from that point in time
to that point in time.
And then we see that the here
that is still your order
but as are valid to some some date
that is not the future,
which means
that this is the day
or the moment
in which that row has been deleted.
So here I can have that information,
but I can even ask to your sequel
to put back all that information
into a single table for me.
So I can say selector
for all my ordering info.
So the table
I have created
for system time
all means show me everything,
everything here. So all the changes.
So as you can see,
I can even have a kind of a duplicate
primary key here,
which is not duplicate
because now we are taking into account
the dimension of time into our query.
So they are not duplicated
because they happen
in different point of time.
That's very interesting. Right?
And so I can see
my first order
with the validity up to here
and it was in progress
then the current value, which is defined
by the fact that is in the near future.
In the far future, is now completed.
And then I can see that
your order was placed header
and it was deleted on the date.
So I can really rebuild everything now
to make sure
to show you
a little bit
to how these simplifies
the ability to query those data.
Let me clean up everything and add
some more data into this fictitious table
so we can have,
you know, more realistic data.
Let me re-enable everything.
And now let's take a look
at the data again.
So here
I just added more and more rows, right?
So I have a forced order
that went through several state.
It was Receiveda
and then it was accepted
and then processed and then blockhead
and then was corrected.
Kind of a typical situation
in their logistic kind of feed right
now.
I you know,
if I come to
you and I ask you to give me
the current data,
you would just say, OK, the order
right now has been closed or it is closed
and that's all I got in my table.
This is the current state,
but I really want to understand
what happened
on the eight of June 20,
21 for this specific order.
So instead of having
to go into the historical table,
since this is
everything is baked into the engine.
So the engine knows about this
need to keep track of changes.
I can just see select from my table
for system time as off.
And so I'm asking the engine
to use the data that were valid
that was valid at that point in time.
So Hugo,
so at
the eight of June
my order was in delivery.
Perfect.
And still as you can see I'm querying
my kind of
do you already have not historical one.
Right. I was going to say
you still have that history table,
but you're never querying
the history table.
The engine just understands that data.
Yes. OK.
So I can choose
let's say, for example,
if I have to do a solution
and I have to create a
changing dimension,
I probably want to query
that table, right?
Because it already contains
all the changes for me.
But if I'm creating HDP solution
where you are,
you're on the front desk
and you just want to clarity data
in see exactly
why we told you that,
you know, the order was blocking for any reason
at this point in time,
you just specify as off and that's it.
So your application is super simple.
Doesn't have to deal with two tables
and you can even do more complex stuff,
like not only querying a single day,
but over time.
So what was the state of the data
over that period? Of time?
So I can. Throw in a range.
Right? Yeah. So that's great.
And I
mean, if you had to do this in the past
or if you do it manually,
you know how complex
this could be, especially the query
path, especially this.
And by having these
bake it in
the engine means for me as a developer,
one thing simplify my life
and make me able to focus on something
that is unique to my application.
This is something
that is provided by SQL.
So I don't have to,
you know, take care of it.
It's already done.
I just have to enjoy it.
And again, I'm more productive.
I can take advantage of all the other
features of that nature of cycle
as a perfect.
Yeah, that and this is
I could see so many places where
where I'm going to use this with,
with some of my customer applications.
Where, where can I go to learn more about
how to
take advantage of temporal tables?
Yeah. So here in temporal tables,
so there is this,
you know, website,
what is documentation website
for your sequel?
You have all the details
about why you should use temporal,
how temporal works, you know, everything.
It's way more complicated
than I just show it.
But you know, here
you can literally know
everything about how the engine behaves.
If you want to purge historical data,
you even can.
Let's say you only want to keep
the last six month of historical data.
You can do all this kind of
configuration here
and you have all the detail,
even some information,
additional information on a how.
For example, use
the historical able
to run analytical query
with columns or index over it.
So go for it.
It's full of information.
That's great.
The docs are so powerful to help.
Yeah.
We've been
learning all about temporal tables today
on Azure.
Fred.
Hey, thanks for watching
this episode of Azure Friday.
Now I need you to like it. Comment on it.
Tell your friends retweeted
watch more Azure Friday.