Episode 106: Temporal Tables

User: What was that record before it was updated?
Me: I don’t know.  Maybe I could restore a backup?
User: Really? You have to do all that? It is just one record.
Me: (under my breath) Yes, a record YOU updated.

If you have ever had a conversation like this you know how difficult it can be to implement auditing of records as they change.  With temporal tables, a new feature, we have the ability to track point in time information about a record without the huge expense of setting up auditing and tracking.  While you won’t want to use this on every table, when there are tables with sensitive data you want to audit from time to time, this feature will come in handy.  We are happy to have Randolph back on the program with us to talk about this new feature.

Episode Quotes

“That ability to kind of see what was there before it was updated, that seems to be the focus of temporal tables.”

“It’s all effective dated, so every row on a temporal table has to have a start and an end timestamp.”

“You don’t know how much space it’s going to use until you start doing some actual real life testing… That’s the biggest limitation for me.”

“When you create a temporal table the history table only takes the clustering index, so mainly the primary and the structure of the table.”

Listen to Learn

03:28 Temporal Tables
05:44 Auditing relative to user tracking
08:20 What are other reasons that people putting in temporal tables?
09:49 Start time and end time parameters for temporal tables
11:39 Using system_time in querying
12:50 Recovering data
13:35 Flushing the history table
14:49 Temporal Table in Management Studio
16:17 History table and clustered index
18:01 Modifying/Altering Temporal Tables
21:17 Data consistency check dealing with corruption
22:44 Scenarios where SYSTEM_VERSIONING = ON
27:50 Other limitations of temporal table
28:39 Is system time applicable when querying joined temporal tables?
31:06 When should you use temporal tables?
32:23 Tips on current feeding side of temporal tables
43:52 SQL Family questions

About Randolph West

Randolph West solves technology problems with a focus on SQL Server and C#. He is a Microsoft Data Platform MVP who has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen or doing voices for independent video games.

Transcript:

Carlos: Ok. Well, Randolph, welcome again to our program.

Randolph: Thank you for having me. I’m surprised I survived the last round but this is going to be interesting.

Carlos: Yes, we actually split that into two. We had you on for a double header and so it’s been about 30 episodes and so we wanted to have you back. Of course we’ve been planning with you for the upcoming conference. The Companero Conference and so we’re excited to have you speak there.

Randolph: I’m excited to speak as well. It will be very cool because the cloud is all very rara shiny but some reasons why you shouldn’t just jump into that.

Carlos: Sure. It’s interesting you bring that up, so I just happen to be reading today, so Microsoft used to be $4 billion, they said $15 billion last year in the cloud.

Steve: So we will be covering how to appropriately take advantage of that $15 billion.

Randolph: That is exactly right.

Carlos: Being milked for a big percentage of it.

Randolph: Well, some of that budget is going towards trying to convince people to go across to the clouds so it’s an interesting time.

Carlos: That’s true. But today ultimately our topic is temporal tables.

Randolph: Temporal tables, yup, one of my favorite features of 2016 and upcoming 2017.

Carlos: Right, and I think ultimately or what we’re kind of getting at here is they try to give us some additional ways to do auditing, right? They tried with some like change data capture and maybe even some other components. I think we all have a lot of the home grown components for audit but that ability to kind of see what was there before it was updated and whatnot. That seems to be the focus of temporal tables. Would you agree with that?

Randolph: I would agree up to a point because temporal tables do not keep track of the user that performed the change.

Carlos: Got you. Ok, very good. So that is kind of the distinction when we talk about the word audit, that the person itself was not captured only the value.

Randolph: Yeah, so temporal tables I’ll quote kind of what Microsoft is saying is that it is for auditing in forensics but you don’t know who the user is that made the change so you will still have to keep track of that. But it also helps, and this is where I really like it for, data that is accidentally change or accidentally deleted. Plus it also provides a point in time analysis of what the data looked like in the past, so you could say, “Show me what the table looked like at midnight three weeks ago”, and then I can see what changed during that time using trend analysis or some type of complex query and we can analyze what’s happening over time using the data that’s in the history table compared against the data that is in the current table.

Steve: Yup, and you know that’s a real important feature there I think would be on the auditing side of it because so often you’ll get a report that shows like the last 30 days sales or something like that and then once a couple of days have passed it may have change drastically and oftentimes you don’t have a good ways of seeing what that looked like last week, or the week before for comparison and temporal table seem like a great way to be able to do that.

Randolph: That’s why it’s my favorite feature of 2016 because now exactly that you can back in time and say, “Hey, this record changed.” And we can see exactly when it changed and if you are keeping track of your logins and things like that this is where the auditing comes in but also with the tracking changes in a more sensible way. There are obviously limitations to how this works because you’re eventually going to run out of space but we can get into that if you like.

Carlos: Do you think, I guess, are those features going to continue to be separate do you think and I guess we’re kind of prognostic getting a little bit here, right? Does the user going to make it to the temporal table feature or is that going to be remain outside of what this feature set is.

Randolph: I think if they want to pursue an auditing feature then they do need to include the user at some stage. Saying that though there are a lot of systems that don’t use system IDs for tracking users. They have their own. For example, when you log-in into a web application your user account may just be a table entry inside the user table so a row and a table. There is no necessarily way a good way of checking which user that is from the temporal table point of view because it’s not a system user that accessing it because there is only one system user if you like. They probably will bring it in eventually. I don’t know. I’m no using any MVP magic here. I don’t know. But they probably will bring it in but at the same time you should be checking that anyway if you have your own table of users that you’re using to check logins. I think you can combine the two and see who’s doing.

Carlos: Well, that’s an interesting point, I guess one that I haven’t considered so having not use temporal tables before. But, yeah, that idea of let’s just account has access to the application but then the application talks to the database with a single account. That architecture, that should change a bit to take advantage of some of that, right?

Randolph: Well, I don’t think you have to change the architecture. I think you just have to be aware that the users accessing the data are internal to the database or to the SQL instance and in that respect you would have to keep track of them separately. Your architecture must have to change but I don’t see temporal tables’ architecture changing if they do add system user tracking at some stage.

Carlos: What are other reasons that people putting in temporal tables?

Randolph: The big reason for me is history, keeping track of history. So one of the systems I built awhile ago was to keep track of all records changes that happen in the system. So whenever a row is deleted or updated, I keep a copy of what it’s used to look like in an auditing table and then if I ever needed to go back into the history I would have to go and query with horrific XML queries to try and figure out exactly what is going on. Because for example if you’re keeping track of all the history for all the tables, you can’t just go and create a beautiful table structure on that, you have to use XML. You’d have to shred the XML to go and read that, and that would either require an index that is 5x the size of your data or a very slow query as it goes read everything. So what this does is in a way that you would create a trigger on a table to keep track of the old row and then store it somewhere. This does exactly the same thing out of the cover. So when you create a temporal table and then you create the history table that is linked to that whenever a row is deleted or updated in the current table the old row will be kept in a history table just like you would do with a normal trigger. The functionality out of the cover is exactly the same.

Carlos: The advantage is it’s going to give you a little bit more detail. It’s going to add that begin and start date or the date parameter too. It’s going to take care of all that.

Randolph: It’s all effective dated, so every row on a temporal table has to have a start and an end timestamp. It’s datetime2, the full 8 bytes, so datetime2 goes down to 100 nanoseconds. That is the smallest granularity and that’s the granularity you have to use for system date times for the temporal tables. So there is a start time and an end time. When you create the table the first time your default value for your create time will be system date time, so current date time if you like. And then the end time will be some time in the future that you’ll never see in real life so the year 9999 December 31st for example. And then if you see a row in the history table that has an end time and then you do not find that row in your current table that means it was deleted. Otherwise if the row exists and the current table it was never deleted, it was just changed. So a little bit of trickiness to understand how things work but it’s all date time specific.

Steve: So then take the example of someone who is not quite on SQL Server 2016 yet and they want to use something like this. They could just create the same thing with their own trigger and with the similar naming convention on the start time and end time and all of that. However, where we get challenging it seems like we haven’t talked about querying it would be when you’re trying to query that data.

Randolph: Well yeah, so the query uses a different type of querying than we’re used to. There is a keyword that you have to use, system_time, so you have to use the keyword system_time in your query so, SELECT whatever from the table FOR SYSTEM_TIME, and then you can do an is at a certain timestamp. You could use a BETWEEN. There is a number of different things that you can use that will automatically figure out the timestamps are between the current table and the history table.

Steve: And to me that seems where some of the real value is and that even though it just triggers under the scenes and all that the value is the way that SQL Server has built that in to be able to query for specific point in time or range or set of values.

Randolph: Yeah, and that’s a value for me as well so I can go and see exactly what the data looked like at that specific point in time down to the 100 nanoseconds. So if we find out that somebody deleted a whole bunch of rows we can go back to the exact point in time before it happened. We don’t have to go and restore an entire database or a file group restore for doing Enterprise edition. But we don’t have to do that anymore. We can now go into the history table, recover all those rows, then flush the history table because otherwise you’re going to have 3x the data that you originally had, and I’ll explain that as well. So if you’re recovering data you go into the history table, you get those rows out, you flush the history table, and then you input the rows back in to the current table then you’ve got your consistent data before the delete happened and you reconnect the history table to the current table again.

Steve: Ok, so you use the term flush the history table there. What exactly does that do?

Randolph: Well, the history table will grow and grow and grow until you are run out of disk space. That’s by design so there is no aging out of the data. You have to do that yourself which is good because if you’re auditing you decide how long you want to keep your data for. Same that though the data is stored in a compressed formats. So they use page level compression on the history table. And because since 2016 Service Pack 1 we don’t have to worry about compression because it’s part of the product in Standard Edition as well so we can have page compression for the history table so it will use less space than it would normally which is one of the advantages of the feature on 2016.

Carlos: Now, remind me, because I guess one of the things that we’ve kind of implicitly said here is that we have a table, now we want to add this history to it. We’re going to use an ALTER Table that’s going to change and we’re going to say, “Hey, this is now the temporal table.” It’s going to create this history table. Now, we
mentioned querying it but from seeing that history table like in Management Studio, is that visible to me or is that still an object that I’m managing or is that kind of just behind the scenes.

Randolph: You can see a temporal table in Management Studio as long as you obviously have the right permissions to view them but it will show up as what’s called a system versioned temporal table because the system is versioning the table. I mean, this is Microsoft’s naming conventions between O and love. What you’ll see in Management Studio is you’ll have the current table. Let’s say for example, dbo.Account, and then in brackets behind it will say system versioned, and then if you expand that out it will show you the history table under that first. And then it will show you the columns keys constraints and all of that stuff that you’re used to. So the history table, you can name it yourself, and put it in its own schema which is what I recommend. Let’s say you have a file group that is on slower storage or cheaper storage whatever and then you put your schema attached to that file group and then you put the history stuff inside that schema then you can manage the history in a different file group which might cost you less money. It will be a little bit slower but it’s history data so you would not have it in the first place.

Carlos: You’re not expecting super fast performance.

Randolph: Exactly. And because it’s compressed you might actually offset some of that performance problem by having it compressed and then you’ll have the list of columns that would look exactly the same as they would in the current table except you cannot obviously have a clustered index. I’m going to change that statement. You can have a clustered index but you cannot have a primary key in the history table because the primary key is in the current table. So the history table does have a clustered index it just doesn’t have a primary key because the primary key is in the current table. Otherwise, the structure is identical.

Steve: And I think the key behind that is that when you’re inserting into that history table if you had the primary key on the same thing as the original table it wouldn’t allow you to put those multiple rows in there.

Randolph: Correct, so what’s happening in the history table is that you have a clustered index that is just not unique because it’s in the pinned only way of writing to the history table.

Steve: So then if we’ve got that history table and I want to clear out some old history in there can I just go in and delete data out of that table?

Randolph: You have to break the link between the current and the history table to be able to do that which isn’t complicated at all. There is just a command and if your editor will bear with me. It’s probably going to be Carlos, right?

Carlos: No, Julien.

Randolph: Hi Julien! Bear with me here.

Steve: ALTER TABLE table SET (SYSTEM_VERSIONING = OFF).

Randolph: Yes, that’s the one, Steve. So modifying temporal tables, you have to have administrative rights to be able to alter a table. You need the admin role to do that. So you cannot alter the objects until you have administrative rights so that’s the first thing. Anybody can read and write from the temporal table and write to the temporal table but only the administrator can modify it. And what you do there is: ALTER TABLE name of the table SET (SYSTEM_VERSIONING = OFF). Then you can do whatever you like inside the temporal table with the full understanding that your auditing trail is now broken.

Carlos: Sure so almost like a maintenance window type.

Randolph: Exactly. So this is exactly the same thing as changing your database from full login model to simple login model is the recovery model so now you have basically truncated or change your history so now you will have to recreate that connection once you’re done flashing the table or deleting rows from there that you need or changing column names or changing columns themselves, deleting columns, updating columns, whatever. Because you may change the structure of your current table and you want to have that change reflected in the history because unfortunately although you can do an ALTER TABLE which changes a column or drops a column on the current table that will take place on the history table. But if you have decided to keep the history of a certain column and you do an ALTER TABLE on the current it will drop that column from the history which you don’t necessarily want. Then you have to break the connection, go into the history table, SET that column to NULL, and then recreate the connection again once you drop the column from the current table. So that way you can keep history of what the column used to have in it but then the column does not exist anymore in the current table so you still got that history retained. Does that make sense or does it just confused you all?

Carlos: No, no.

Steve: I think it make sense.

Randolph: Ok, good. There is also another feature which I have never turned off. When you create the SYSTEM_VERSIONING on a table you can alter an existing table and create a temporal table out of it, which Carlos alluded to earlier, or you can create a new table from scratch which is a temporal table. And to change an existing table you have to ALTER the tables SET (SYSTEM_VERSIONING = ON) then you can assign it to history table name if you want to create something in your own schema that we spoke about. And then there is another feature called data consistency check = ON. The documentation says this should always be ON to ensure that the history table follows the correct structure format as the primary table. I’m not sure why you are going to turn that OFF. So I’m not sure why the feature is even presented as an option.

Carlos: Right, because you made a change to that table, data type change most likely or I guess you can add a column.

Randolph: But why have the history table if you’re not checking the data consistency? I’m not sure why that’s there but I’m just letting you know that it is there. It is required with the ALTER TABLE statement if you’re going to use SYSTEM_VERSIONING = ON but I’m not quite sure why it’s there.

Steve: So one thing and I haven’t use the data consistency check one way or the other but one thing I was reading about on a blog post was that it was there to help deal with corruption at some point which immediately I perked up a little bit and thought, “Well I need to understand this one.” But I haven’t had a chance to dive into that yet. Have you heard anything about using that around corruption?

Randolph: I have not but I would imagine that if you have a corrupt table, and this is all conjecture on my part. If you have a corrupt table and you have a history table that contains all the history, the history should probably be fine because it’s stored in different file group for example or different part of the file anyway. You would have access to that history without necessarily reading or writing whatever is in the current table using just standard queries. I believe that the work was a bug in one of the Cumulative Updates did fix a bug in data consistency check for certain tables, so I’m not sure if that’s what you’re referring to, Steve.

Steve: Yup, and I guess that’s something that I need to look into a little bit more.

Randolph: Because I do know that there was in Cumulative Update 1 there was a bug with data consistency check so maybe that’s what you’re referring to.

Steve: So let’s take an example here then. Let’s say we create a table and we have SYSTEM_VERSIONING = ON and we have the history table associated with that then we insert a single row into that table. Does that row then exist in the table as well as the history table at that point or does it wait to get to the history table until it ends up being changed?

Randolph: An INSERT will only affect the current table. The only time that the history table is involved is if you modify that data. So when you insert a new row into a table there is no reason for it to have changes at all so you’ll only see it in the current table and then when you modify it the timestamp will be kept track of and the original row with the start time that you created the row in and an end time of when it was changed will show up in the history table.

Steve: Ok, got it. So then if you’ve got a table then for instance it’s something like bank account transactions which is probably the type of table that’s not going to change frequently. It will have a lot of inserts but very rarely I would hope our transactions in the past being modified.

Randolph: Well, I’m going to stop here and say that’s a bad accounting practice if you’re changing because you should be posting changes in an append only model with accounting.

Steve: Sure, exactly. But if you wanted to track to see if anything was actually changing it, you could turn this on, go back and look and see what point in time things were changing if there were. But then I was going to sort of flip it around to a different example of let’s say you have a table that’s like your users table and that user has username, and a last login date where every time that user logs-in it will update the last login date. That would be one that if temporal tables were turned on every time the user log-in it would be inserting another row into that history table, and it could grow very quickly over time.

Randolph: It could, yes. And that’s why the page compression is going to be very helpful from that point of view because that data should compress very well. But saying that it may not be a good choice for a temporal table although it might be a good choice if that’s what you want to do is audit and see how often people are logging in and things like that.

Steve: Right. So then in the case that you have a very narrow table, maybe there’s just username and last login date, something like that, primary key, whatever you need there too as well but if that’s probably going to work better in a history table than if you’ve got a user table that might have a 150 user attributes to find in different columns. Again, a poorly defined table there because when it logs to the history table, of course it has login the entire contents of the row. Is that correct?

Steve: Correct, so wide tables are going to be slower because it’s exactly the same effect as if you were taking from that and using a trigger to write it to a different table. It’s still going to be a wide table, possibly wide columns as well so it’s going to take awhile to write and you are going to have a performance impact on your current table because it has to commit the row to the history table first obviously. Let me just confirm that. Actually I do a blog series in November 2015 so it’s fairly fresh in my mind. I just want to double check here. The row will be modified in the current table and then it will be modified in the history table. It’s part of the same transaction though.

Steve: Alright, so are there other limitations that people should be aware if they are going to try this out. The big thing for me is running out of space because it’s very easy if you’re not use to what kind of data or what the…

Steve: I think we’re relying on Julien a lot on this one, Carlos.

Randolph: Well, how many times did we pause the last one? That was pretty crazy.

Steve: Quite a few.

Randolph: Can you repeat the question, Steve.

Steve: Ok, what I was just going was other limitations?

Randolph: The big one for me is the disk space because you’re going to run out of space quickly. The other problem is if you are using this in a staging environment and you have more than one table with a foreign key relationship and both of them are temporal tables. You’re going to have a hard time trying to pin down history queries to say what the data looked like at this specific point in time because of those foreign key relationships so it gets a little bit hairy in that respect. You don’t necessarily want to have every table in your database a temporal table because it’s going to make your queries complicated and it’s also going to slow you down because it’s going to be querying the history over time.

Steve: Ok, so in the SELECT statement then when we were querying for temporal data where say FOR system time as of and specific date time too. Now, if you do that in SELECT FROM one table INNER JOIN another temporal table, so we have two of them there. Is that system time parameter going to apply to both of the tables and the join?

Randolph: What a fantastic question. I’m going to say that that time is specific to each table. What I would do is I would run an experiment and then find out because I don’t know.

Steve: Ok, it’s one of those new features where there is a lot of experimentation to do there I think.

Randolph: Where the challenge comes in is you may have history on a lookup table for example. So the lookup table has a whole bunch of stuff that might change once in a while and you don’t necessarily look at the history when you’re joining that to something else, but you might have to do that and it gets a little but hairy. It’s not difficult, it’s just time consuming for the engine to go back into the history table as well, and then because when you do a temporal query. I’m going to call it a temporal query and not a history query. When you do a temporal query against your table it’s going to combine the results from your history and your current. If you’re asking for everything so it’s going to do basically UNION ALL or a UNION because the data shouldn’t be the same. When you combine the two, if you’re going to do a point in time query it’s going to go and look in the history to see if anything is there so it’s always looking in that history table. So if that is on slow performing hardware then it’s going to take longer, that’s kind of where I’m getting to.

Steve: Ok, that makes sense.

Randolph: I was just going to say when should you use temporal tables? You should use temporal tables on data that needs to be tracked and that you were tracking in an interesting way before. For example, my system that was keeping track of the XML data version of history. I needed to check that for auditing reasons. That is a good reason to use temporal tables. But I wouldn’t just go turning it on everywhere because eventually you’re going to forget and eventually you’re going to run out of disk space somewhere.

Steve: Yes, and hopefully you know about that before you run out. Hopefully you have alerting in place.

Randolph: Right, you should have alerting in space but if you’re playing with it on a test environment or something and you forget about it. You may need a massive amount of space to handle that data. Some people might have to retain 7 or 10 years of history. And then you may have to age in the manual process anyway. So you don’t know how much space it’s going to use until you start doing some actual real life testing. That’s the biggest thing that I have to say. That’s the biggest limitation for me. I think I said that a hundred times.

Steve: How about any other tips on sort of current feeding side of temporal tables?

Randolph: Well, back to the disk space that’s being used. If you are creating a lot of churn and you’re temporal table is growing excessively you may want to think about going into the history, breaking the connection and deleting or aging out data that is older than a certain number of months or years. And it’s got indexes on it so you might want to do some index maintenance as well. You can apply indexes to columns inside the history table manually if you like by the way which is pretty cool. But you cannot change the clustering key so you may want to do some index maintenance from time to time on the history tables.

Steve: Ok, so if you have a history table associated with your temporal table and you add indexes, non-clustered indexes to that for instance, do they automatically end up in the history table or do you need to go and add those manually if they are needed?

Randolph: When you create a temporal table the history table only takes the clustering index, so mainly the primary and the structure of the table. They should be no reason for it to take the indexes across because that’s additional maintenance that’s not necessary because you’re not necessarily be querying that table a lot. So it will only copy the structures, so the columns themselves and whatever your primary key is will become a clustered index on the other side. There will not be any indexes that get copied across. If you do want to query that table frequently you would have to add your own indexes.

Steve: Right, so in that example if we are querying for a certain point in time and filtering on a column that needed an index, if that history table got large it could be quite slow if we don’t add that index themselves.

Randolph: Yes, and there is a whole can of worms here with filtered indexes which don’t always work the way you expect them to which we won’t get into. I think we covered that in Episode 81.

Steve: Yeah, great, great. So any other current feeding tips to add at this point?

Randolph: When you make a change to your current table the change if it is structural, so you’re deleting a column or modifying a column may affect the temporal table as well, the history table. And the reason for that is because it wants to keep a consistent check between the two. That’s the data consistency check that’s happening. So if there is data in the history table that doesn’t satisfy the requirements of the new column, for example you may have just deleted everything from the current table and then alter the column. If it is attached to the history table still and you do and alter column and it then tries to change the data type, if the history table contains a data type that is incompatible with the new change it will fail.

Steve: Oh, so you either purge the history table or know that column in the history table perhaps in order to be able to modify it in the original table.

Randolph: For me personally I would break that connection completely, rename that history table so that it’s archived and then create a new history table going forward if the data types are that significantly different because effectively it’s a new table. If you do want to go and query that history you still can by querying the history table directly in the archived version. But if you’re going to be making structural changes to your current table, I would break the connection, rename the history table and just create a new history table going forward. The other thing that I wanted to mention, we touched on very briefly earlier, is if somebody deletes, let’s say runs a delete statement against the current table without a WHERE clause so every single row gets deleted. What you will find is that the original version of that row for every single row will find its way into the history table. So if you’ve got 10 rows, that’s fine. If you’ve got 10 million rows, you’re going to have 10 million rows in your history table as well. So if you want to recover that data you can just copy that over from the history table into the current table but I do recommend breaking the connection first because any modifications you make to any rows after that will also be written back to the history table and you still got millions or 10 million changes in that history table. So either flush those rows out first or rename the table or do something but don’t try and go and copy those 10 million rows back into the current table from the history table without doing some maintenance on the history table because otherwise you’re still going to have those 10 million records there waiting in the history forever and ever.

Steve: Yup, so it really seems like it just keeps coming back to the history table can get really big depending on what you do and it’s maybe slow if you don’t index, it may run you out of disk space. I mean there may be any number of issues associated with that history table becoming gigantic.

Randolph: Well, it’s a usual story, how much are you querying it? Does that mean we’re going to be using a buffer pool for that table? All of those things that go along with that because even though it is compressed on disk whenever you read it into memory it’s going to be uncompressed.

Steve: So another question around this. Let’s say you’re querying your original table and you’re not using the system_time for system time parameter, you’re just doing a direct query of your source table and your temporal table without the history. Does that work exactly the same as it did if you did not have a temporal table?

Randolph: That’s next in question because it brings up another thing that I didn’t mention. Yes, it works exactly the same as if nothing had changed. So when you create a new temporal table you can create the start and end time using the datetime2 data type and you can mark those columns as hidden. There is another feature in 2016 for hidden columns and this is specifically for supporting this feature. So you hide those columns so that any queries that do, and you shouldn’t do those what people do a SELECT *. Those columns will not be visible in a SELECT *. They will not be visible in any existing applications that don’t know that they are there because the system is managing them. It’s as if you’ve created a column with a default value because that’s effectively what it is.

Steve: But those have to be specifically marked as hidden for them to not show up in the SELECT *.

Randolph: Correct. But if you do that and you can do that very easily when you’re doing the CREATE or the ALTER table to make it a temporal table, you can mark those columns as hidden. So it has no effect on any current applications or any queries that you might be using currently. It will work exactly the same way as it did before. So unless you’re using that system_time query keyword it’s going to be carry on reading from the current table as if nothing else has happened.

Steve: Ok, great.

Carlos: Good deal, lots of good stuff today. As we’re going to finish up here, I know we’ve done SQL Family before, but can we go ahead and hit it again?

Randolph: We can do it again because my answer may have changed.

Steve: So Randolph how did you first get started using SQL Server?

Randolph: Accidentally, as with everybody I know. Nobody I know has ever wanted to be a DBA and then they realize they’re being silly because it’s actually what they wanted to do all along. I used Access, I used MySQL, I used Ingres. I used SQL Server when I was working with PeopleSoft back in the 90’s. I didn’t know what it was. It’s just one of those things, oh this just keeps your data, it’s in an Excel
spreadsheet basically. Lots and lots of Excel spreadsheets. And then I started reading about corruption because that happened to two of the clients. The databases either got dropped or corrupted in some way and I start of getting interested in that way. And basically it’s just, it was there, it was there, it was there and let me have a look at it and then I started loving it. Access was really my entry point into SQL Server because Access was so terrible.

Steve: You know, that’s so interesting because at first glance if you don’t know databases very well, Access looks pretty darn cool.

Randolph: Access has some very nice features for front end development for a data backend. Well, it did. I mean it is 2017 now.

Steve: Sure, but back in time it did.

Carlos: Well, they have it all in one application too, right? Two things.

Randolph: Exactly, and it was easier to use in FoxPro. It was cheaper than FoxPro and you didn’t have to be a programmer to write a query because it had that cool query designer that you could do. I believe SQL Server has the same thing but I never used it. It was just really neat to be able to picture your data with a graphical interface.

Steve: I wonder if Microsoft realized that access is really a stepping stone to get to SQL Server from… I’m sure they do.

Randolph: It’s a gateway drug.

Carlos: If you could change one thing about SQL Server what would it be?

Randolph: Last time I think I said that I want to do a TempDB for every database. I still kind of think I want that but I’m not sure if that’s the one thing that I want to change now because firstly Azure SQL Database has come a long way and in a very short time so if I would change one thing. It would be making Azure Database cheaper. Because I think there is a lot of potential for people to use for single application usage. For example using an Azure Database to replace what people use MySQL for now and that would be web applications and things like that. If it were a little bit more cost effective with all the feature set that they have because everything that’s in SQL Server 2016 is in Azure SQL Database including temporal tables on the Premium Edition and in-memory OLTP and all those things. So I think temporal tables are actually supported on the Basic Version for what it’s worth.

Carlos: I think you’re right. It’s interesting because you look at the, what’s the edition that only allows you to go 10GB.

Randolph: That’s Express.

Carlos: Express, thank you.

Randolph: I have a funny story about Express off the topic. A customer of mine had a customer because they are a vendor. A customer was running their product in Express Edition. They had 20 databases and of course Express has auto close enabled by default so a typical diagnostic session which takes 5 minutes normally run for 2 hours because it had auto close on every single database. So every time a query run there we close the connection then open it again for the next query. It was fantastic. So what were you are going to say about Express Edition?

Carlos: Well, I guess I’ll say because it’s free, right? At least my impression is of making like Service Pack 1 some of those analytic features available in the Express Edition. That is a direct response to MySQL ecosystem, so I think to your point of like, “Well, hey that’s free.” Now there is compute, right, so I guess there should be some cost there. But a database under 10GB should be pretty close to free, right?

Randolph: Agreed, but…

Carlos: That might be the change there. But I don’t think a lot of people are going to take that into consideration because even if they had to pay a penny it’s still I had to pay something and this is “free”.

Randolph: Exactly. The other cost with Express Edition which I love is the 1GB limit of memory so non sense.

Carlos: Oh right, I forgot about that.

Randolph: MySQL doesn’t have that limit obviously but it is a strong consideration for not using Express Edition. You’ve only got 1GB of RAM.

Carlos: Is that still a case in 2016?

Randolph: Yes.

Carlos: Ok.

Randolph: 4 CPU cores or one socket which are very small, 1GB of RAM and 10GB database size.

Carlos: Got you.

Randolph: I would like to see a better costed version of Azure SQL Database for the Standard level if you would like. And get rid of Basic because I don’t see the point of Basic. 5 DTUs isn’t enough for anybody. I’m sure I just coined a new phrase. And then just have Standard and Premium, and have the Standard do more for less basically because I think the adoption would be higher if they did that.

Carlos: Well, they’ve been coming down. I know the sizes have been getting larger and so, yeah, and so maybe another year or so we’ll get there.

Randolph: Maybe by the time this podcast goes up.

Steve: You’ll never know. What’s the best piece of career advice that you’ve received?

Randolph: I’ve been working for so long I probably forgotten most of what I’ve heard but the one piece of advice I give to people because I do mentoring as well is to say, “Be honest”. So if you screw up tell somebody that you screwed up. It’s much easier to handle a problem if you’re completely honest and say, “This is what happened, this is how I created this problem.” And then it’s much easier to fix than lying and say, “Oh, I don’t know how that happened”, or “I did not kick the power supply out of the server”, or “No, I did not accidentally bumped this hard drive off the disk”, you know stuff like that. Just be honest and it’s just easier for people to handle the fallout. And if you’re honest it shows that you are human and there is nothing wrong with doing something wrong. I make mistakes all the time the difference is I own up to them. I’m not like, I’m not going to say it. I’m going to get political. I’m going to stop here.

Carlos: But it’s interesting on that one because I’ve heard people say that they are sometimes afraid to be honest because the environment they work in is too aggressive or management doesn’t have your back on anything.

Randolph: Well, then I wouldn’t be working in that environment. And being honest and saying, “Yes, I did this”, gets you fired. Well, then any place is better than working in an environment like that. Toxic work places are very bad for your health and you will die young.

Steve: Yup. And I think what I was going to say around that is that if you’re on that toxic work environment, and I’ve been there in the past, honesty is even better at that point. I mean, it’s always good but it’s even more important at that point because you’ll know exactly where you’ll stand.

Randolph: It’s refreshing. It might not be the outcome you expected but it is refreshing for everybody to say, “Well, this person is telling the truth and they’re not going to compromise whatever for the sake of looking good.”

Steve: Yup, very good advice.

Carlos: If you could have one superhero power, what would it be and why do you want it?

Randolph: Last time I said, I have a superpower wherever I walk into a server room a machine will either crash or not, that’s a superhero power I didn’t want. But there’s been a whole lot of superhero movies recently, a whole lot of stuff that I don’t enjoy because everybody is destroying everything. The one superhero I do like is Batman because he is not a superhero. He is just a guy. He does have compromised morals and he’s very very wealthy. I don’t want to be wealthy like him because that also corrupts you. But I do like the idea of just being a decent person. I’m not necessarily killing bad guys but if you see something going wrong, speak up say something. You don’t have to be a superhero to be Batman. So you don’t have to be a superhero to say, “That’s wrong, this is not cool and do something about it.”

Carlos: There you go.

Steve: Yes. I like that.

Carlos: Thanks so much for being on the program today.

Randolph: Thanks for having me and I’m sure this would have been a lot longer without the edits. Thank you for making me sound good.

Carlos: No, it’s great to have you on again and chat with you. And yeah, we look forward to seeing you in October.

Randolph: Sounds good. I’m looking forward to the Companero Conference in Virginia, in Norfolk, on the 4th and 5th of October, 2017. And it’s a single track so you just have to stay in the same room and listen to people explain how awesome SQL
server and the cloud is.

Steve: We’ll see you there.

Carlos: That’s right, you took the words out of my mouth.