I’ve been determined to produce a SQL Expression that was able to tell you when all the feasts and saints days are. In the following example, I’ve only put the major feast days that were generally celebrated in Britain before the reformation, but it is very easy to add or take away what I’ve given you to taste.
A fitting topic for America’s premier feast day.
Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way.
This week, we are going to look at how to modify a temporal table that already has data in the history table.
If you’re using temporal tables for auditing, it’s important to know that yes, data can be modified.
Temporal tables are new type of database tables introduced in SQL Server 2016, these tables are system-versioned and keep history of changes (insert, delete, update) of everything happened on data rows. Retrieving change log from these tables are easy. These tables can simply tell you what was the data at specific point of the time in the table. These tables works with datetime2 columns to keep FROM DATE and TO DATE information of each change. This means these tables can be used for implementing changes in dimensions, yes you know what it called; Slowly Changing Dimension!
Slowly-changing dimensions in an OLTP scenario was the first use I could think of for temporal tables, so I’m glad Reza put this article together.
TDS — Tabular Data Stream — is the protocol that SQL Server talks with its clients. This is a proprietary protocol, owned by Microsoft (and Sybase, who have their version). Nevertheless there is exists FreeTDS which originally was a reverse-engineering effort of TDS. Now when Microsoft has published the TDS specification, they should be able to repair any cracks they may have. Check out the FreeTDS home page for further details. There appears to be a DBD::FreeTDS that goes along with it.
I used FreeTDS to connect to SQL Server from RStudio, so I endorse that method.
Curated SQL will not be taking Thanksgiving off. To compensate for the pace of blog posting activity usually drops around Thanksgiving, I’ll link to a few classic articles. That way, even if you’re in the office, you’ll have something to occupy that extra time.