One Way To Track Row Changes In Place In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the world of tracking changes in SQL Server tables using temporal columns—a feature that can help you monitor when data modifications occur without the complexity of full-fledged temporal tables. I start by explaining why change tracking is often a problematic choice and highlight how change data capture (CDC) simplifies setting up change tracking for new or existing tables, though it comes with its own set of challenges. Then, I demonstrate how to add temporal columns to an existing table to track modifications, emphasizing the importance of hidden junk date columns and default constraints that help record when a row was last modified. Through practical examples, I show how these changes can be implemented and queried, providing insights into their usefulness for data auditing and movement processes.
Full Transcript
Erik Darling here with Darling Data. Of course, I forgot to adjust my camera before I go to recording, so we’re going to make sure that we are crisp, clean consultants for this video. And I’ve been told that I should do this at the beginning when people are still watching. Apparently it’s more effective. If you like SQL Server performance tuning content or just SQL Server informational stuff in general, and you end up liking this video, you can like the video and you can comment on it. You can also subscribe to my channel and subscribe to my channel. if you want to be notified whenever I publish these things. So, uh, we’re doing that upfront this time. I’m not going to thank you for watching because you haven’t watched it yet. In this video, we’re going to continue on with, uh, they call that A-B testing in market research. Uh, in this video, we’re going to continue with the theme of yesterday’s, the last video, not really yesterday yet. Uh, uh, that was deep. Uh, with different ways to track changes in SQL Server. So, uh, in yesterday’s video, we covered the fact that change tracking is one of the worst features that Microsoft has ever added to SQL Server. And, uh, that change data capture was fantastic. Uh, in this video, we’re going to talk about how you can use temporal columns, not temporal tables. Temporal tables have way too many problems for me to, for me to say, yes, you should use them. Uh, but temporal columns can help you make this a little bit easier. Now, full disclosure, uh, just, you know, change data capture is a lot easier to set up for a table, uh, because you just start tracking new changes and all that stuff. If you wanted to like add this to an existing table, especially if it’s a big table, it could be painful to add these columns because you are adding not null columns with default constraints, which means a whole lot of data pages get written to. But if you have a brand new table or you’re designing a table and you want to be able to track when data change in that table, this could be a good way to do it. Uh, now keep in mind this, this of course, unless you’re using soft deletes, this will not change when something got deleted. You know, soft deletes will track like when the change happened to a row, like when you change is deleted from zero to one. Uh, but this won’t like if you delete a row out of the table, this won’t check that. This won’t track that you would need something more robust, like change data capture. But if your only goal is to figure out when a row got modified, this is a pretty good way to do it. So what I’m going to do is I’m going to, uh, drop this table if it exists and then also create this table. And this table is going to have all of the hallmarks of a table with, uh, for the, they would, they would be set up as a temporal table, but there’s no history table for it. We’re not assigning a history table. We are adding two columns to it that you need to have, but this is, this is really important. We’re going to talk, talk about two important things at this juncture.
One, we have this column called junk date and we don’t really need junk date. This, this doesn’t do us any good. So we’re marking this as hidden. We don’t want this to show up in select queries. We don’t need it. It’s useless to us. The other thing I’m going to cover is this. So we have a default constraint on the column that we do care about that shows us when something was last modified. I have sysdate time here because this will help me know exactly when a row got into a table, right? That’s, that’s, that’s useful in some cases. In other cases, you might only care when a row changed in the table.
If that, if that’s the case, you might not, it might look kind of weird and confusing to have a current time in that row or a current date in that row because that might’ve just shown up in there. We don’t know if that’s when anything actually happened or not. So you, if you don’t, if you want to know like that a row actually changed at some point in time, having a current date in there might be a little confusing.
What might be a little less confusing is if you put like a really old date in there, like 1900, oh, one, oh, one or something. So you could, you know, that obviously wouldn’t tell you what that the, when the row got into the table, that might just give you, I mean, unless you’re, you, you had a SQL Server in 1900, in which case, share your time traveling secrets with me, dear leader. I have some ideas.
One of them is called the stock market. Sounds like a winner. The other one is becoming a credit card company. That sounds like a, that’s another good one. That’s, I got goals, you know.
Anyway, so these are the two columns that we have to add to the table in order to make this whole thing work. And then of course we have this period for system time, blah, blah, blah thing, because apparently that’s just, that’s just syntactically correct. So let’s stick some rows from the votes table into the votes track table.
Not a whole lot of them, just about a year’s worth. And then we’re going to look at what data ended up in there because, I don’t know, it seems like a reasonably fun thing to do. And of course this insert is taking its sweet time, even with a tab lock hint, screw you SQL Server.
All right. So let’s take a quick view of what’s in this table. This is everything for vote type ID 7.
And you’ll see that we have a last modified date over here of the day and time that I’m recording this. And since we have bulk inserted this data in, these are all going to have the same value. If you were inserting like a single row at a time or like two or three rows at a time, they would all have the same value.
But, you know, this, because it was just a big whopping insert, they all have this last modified date of today, which is not yesterday yet. Very deep, very deep thinkers here at Darling Data. And so we got that and that’s all well and good, right?
We have that. So let’s look at, so right now, let’s just make a mental note that this is 2024 08.01 at 18.02 in 34 seconds, right? And now let’s update the bounty amount column and set that to four nines, which is not three sixes, four nines.
We’re not getting weird here. And let’s look at what’s in the table now. All right.
So this is obviously incremented by however long I was talking for, because if we look at, you know, the results of these two things, they will have slightly different times. So we did not affect vote type ID 4, which still has a last modified date of 18.02. And the last modified date for the rows we did change is 18.03.
So this is another way of figuring out when data changed in your table. If you need to take that data and either, you know, audit it, be like, hey, this looks funny. It changed.
When did it last change? Huh? Like, you know, you know, it’s not going to tell you who changed it. It’s not going to tell you what the previous values were. So it’s not really, like, good for, like, a data auditing scenario. But it is good if you need to take this data and put it somewhere else and you need to figure out, you know, again, sort of like we talked about with change data capture.
Let’s say you have a process that will look at this table every X minutes and look for data that has changed since the last time it ran and put that data into another table. Maybe it will aggregate it and do something else with it, but this is one way that you can figure out, hey, these rows changed since the last time this process ran. I need to take these rows and move them over.
And it’s kind of cool for that because, you know, it’s not change tracking, which is the worst feature that Microsoft has ever added to SQL Server. Or at least, you know, probably top five worst features. Again, I’ve seen that thing cause more trouble than it’s, I don’t know.
It’s just brutal. And, yeah, so this is just kind of another fun way to do that. And I’ve used this with a few clients to help them with, you know, data movement processes, which, you know, they turned out to be pretty happy with.
And happy clients is what I aim for, right? Again, clients, the nice people who pay me to make these free videos. If you would like to hire me so that you can support this channel and I can keep making free videos, you know how to find me.
We already talked about liking and subscribing and commenting, so we can skip that part in case you forgot because some of you people are a little forgetful up here. But anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you in the next video, which will not at all be about tracking changes. It just might be about considerations for implementing soft deletes, which would actually kind of go hand in hand with this. So, we’ll see how that goes, I suppose.
Maybe I’ll even see you there. Maybe you’ll even decide to subscribe and get notified for when that video comes out. Because I promise you, you will learn something.
I hope. I hope you will learn something. Or you will enjoy yourself. Or both. Lots of options. We have lots of potential here.
You and me, we’re going to be together for a long time. Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.