A Fine Slice Of SQL Server
Andy Brownsword gets around the limitations:
In a Managed Instance, the SQL Agent job history is fixed at 1000 records or 100 records per job. This isn’t configurable like a regular SQL Server install. So how can we maintain a history of these if we want to retain those records?
There are 3 approaches which could be worth considering. Two of these have been well covered by others and the final one I’ll demonstrate here:
Click through for those three techniques.
Comments closedErik Darling is keeping an eye on you:
Sort of recently, a client really wanted a way to figure out if support staff was manipulating data in a way that they shouldn’t have. Straight away: this method will not track if someone is inserting data, but inserting data wasn’t the problem. Data changing or disappearing was.
The upside of this solution is that not only will it detect who made the change, but also what data was updated and deleted.
Read on to see how it works. I’ve used temporal tables for this type of scenario, and they’re fine for stable table designs.
Comments closedMatthew McGiffen gets to one of my problems with temporal tables:
I also referred to Temporal Tables which are available to us from SQL Server 2016 onward.
Temporal tables aren’t just about monitoring change, they also provide really nice methods for being able to query historical data – to see what the values were at a particular point in time e.g.
SELECT * FROM dbo.SomeData FOR SYSTEM_TIME AS OF '1900-01-01';
My big problem with temporal tables is that they only implement system-defined times. That’s fine in a quasi-historical OLTP scenario, where you want to track history but only occasionally make use of it. But if they supported application time, then you have the ability to create something akin to a type-2 slowly changing dimension with just a few extra words. I understand that the tricky part is that application-defined temporal tables lose the nicety of knowing that the latest insert always goes into the main table and drives the prior record into the historical table, but there are some clever ways around this problem as well. It’s just too early in the morning for me to articulate them is all…
Comments closedDaniel Hutmacher does a little locking down:
You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.
What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.
But how to you version the contents of a table, while hiding things like deleted records from prying eyes?
There’s not a whole lot we can do, but Daniel shows what we are able to do.
Comments closedGarry Bargsley can’t wait to truncate:
Today, I will discuss a gotcha I ran across with SQL Server Temporal Tables. In my day-to-day environment, we do not use Temporal Tables widely. However, a few are in use, and a few are coming down the pipeline for new projects.
Click through for the gotcha, as well as how you can work around the issue.
Comments closedBob Dorr troubleshoots an issue:
When 2 or more workers are modifying the same row, it is possible to encounter 13535. The time of the begin transaction and the modification are the defining properties. When the transaction begin time is before the latest row modification, error 13535 is encountered.
Click through for an example of how you might trigger this error. This ultimately is the optimistic concurrency problem: how do you deal with multiple writers when using snapshot-based optimistic concurrency? Silently clobber or raise an error? Looks like temporal tables, like memory-optimized tables, raise an error instead of going quietly into the night.
Comments closedRayis Imayev notes a problem with Azure DevOps deployments:
Here is one thing that still doesn’t work well when you try to alter an existing temporal table and run this change through the [SqlAzureDacpacDeployment@1] DevOps task, whether this change is to add a new column or modify existing attributes within the table. Your deployment will fail with the “This deployment may encounter errors during execution because changes to … are blocked by …’s dependency in the target database” error message.
Read on to see what causes this problem and what we can do to work around it.
Comments closedLee Markum continues a series on temporal tables:
In the initial post, we talked about some very basic items to get you up and running with temporal tables. In part 2, I’ll cover a few more things to think about and implement when working with temporal tables.
In the first post, we looked at altering an existing table to be a temporal table. Now we will look at what is involved in making a table be a temporal table from the very beginning.
Click through for Lee’s thoughts on naming, indexing, and data retention.
Comments closedDaniel Hutmacher has an interesting use case for temporal tables:
You have a table that you want to add “created” and “updated” timestamp columns to, but you can’t update the application code to update those columns. In the bad old times, you had to write a trigger to do the hard work for you. Triggers introduce additional complexity and potentially even a performance impact.
So here’s a nicer way to do it, trigger-free.
Click through for the solution, as well as a warning from Daniel.
Comments closedHugo Kornelis wraps up a discussion of temporal tables with miscellany:
Of course, it will be a quite common requirement to query products and their suppliers. When querying the present, you can just access the Suppliers and Products table without the “FOR SYSTEM_TIME” keyword, and the execution plan will unsurprisingly show that a regular join of the two “current” tables is used, with no reference to the history table. Nothing special. We already saw in the earlier parts that querying the present simply ignores the history table; joining does not make that different.
But what if, for instance, we want to show all data as it was valid on June 3 at noon? Well, that is also simple. We already know that we can use FOR SYSTEM_TIME AS OF” to get the rows from a single temporal table as they were at a specific time. We can use that syntax for both tables, to get the data we need:
Read on as Hugo dives into some messy problems. Temporal table queries can expand out in complexity very quickly, as this post shows.
Comments closed