How to Configure SQL Server Temporal Tables Part 2

 

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.

Designing a Temporal Table from Scratch

First of all, you can create a table to be a temporal table from the beginning, without naming the history table. But you get an ugly history table name in the following format:

[dbo].[MSSQL_TemporalHistoryFor_tableObjectId].

In my test case that turns out to be this:

[dbo].[MSSQL_TemporalHistoryFor_1913773875].

Also, notice that in this example below, the SysStartTime and SysEndTime required columns are not designated as hidden. This means they can be returned in queries, so be aware of this, particularly when using SELECT *.

This syntax also results in a clustered index being created on the history table
using the SysStartTime and SysEndTime columns.

Observe that I’m taking advantage of the inline index creation syntax that was new in SQL Server 2014. Look at examples S, T, and U to see more information on how to use this feature.

CREATE TABLE dbo.Employee
(
ID INT IDENTITY(1,1) NOT NULL,
LastName VARCHAR(100) NOT NULL,
FirstName VARCHAR(75) NOT NULL,
JobTitle VARCHAR(50) NOT NULL,
BirthDate DATE NOT NULL,
HireDate DATE NOT NULL,
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
 PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (ID),
INDEX IX_LastName_FirstName (LastName,FirstName),

)
WITH (SYSTEM_VERSIONING = ON);

ALTER TABLE dbo.Employee ADD CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME() FOR SysStartTime;
ALTER TABLE dbo.Employee ADD CONSTRAINT DF_SysEndTime DEFAULT SYSUTCDATETIME() FOR SysEndTime;

This allows me to create the index on Lastname, FirstName directly in the CREATE TABLE statement and not have to do that afterward in a lengthier syntax.

INDEX IX_LastName_FirstName (LastName,FirstName)

To clean up this example and try a slightly different approach, run this code:

ALTER TABLE dbo.Employee SET(SYSTEM_VERSIONING = OFF)
DROP TABLE dbo.Employee

 

How To Name the History Table

You can create a temporal table and name the history table so it is more human friendly. This syntax also results in a clustered index being created on the history table using the SysStartTime and SysEndTime columns.

If you want to be able to create a clustered columnstore index, or a different clustered index then there are 2 choices:
1. Use this syntax and then drop the clustered index and make the new clustered index.
2. Create both the temporal table and the history table and when you make the history table, and specify the indexes you want to create.

 

CREATE TABLE dbo.Employee
(
ID INT IDENTITY(1,1) NOT NULL,
LastName VARCHAR(100) NOT NULL,
FirstName VARCHAR(75) NOT NULL,
JobTitle VARCHAR(50) NOT NULL,
BirthDate DATE NOT NULL,
HireDate DATE NOT NULL,
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
 PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (ID),
INDEX IX_LastName_FirstName (LastName,FirstName),

)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.EmployeeHistory));

ALTER TABLE dbo.Employee ADD CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME() FOR SysStartTime;
ALTER TABLE dbo.Employee ADD CONSTRAINT DF_SysEndTime DEFAULT SYSUTCDATETIME() FOR SysEndTime;

DROP INDEX IX_EmployeeHistory ON dbo.Employeehistory;

CREATE CLUSTERED COLUMNSTORE INDEX IX_CC ON dbo.EmployeeHistory;

 

Indexing Decisions for Temporal Tables

Per MS Docs, if you are creating this table as a temporal table for the purpose of auditing row changes, then a clustered rowstore index on the  SysStartTime, SysEndtime and the PK columns of the temporal table is a good choice. Otherwise, a clustered columnstore index is a good choice for analytic queries.

How to Handle Data Retention for Temporal Tables

The simplest option for managing data retention on a history table is to use
the HISTORY_RETENTION_PERIOD = syntax. The available time units for history retention are DAYS, WEEKS, MONTHS, YEARS. SQL Server then uses this information to manage the deletion of data from the history table for you. notice below I have added HISTORY_RETENTION_PERIOD = 6 MONTHS to the syntax.

CREATE TABLE dbo.Employee
(
ID INT IDENTITY(1,1) NOT NULL,
LastName VARCHAR(100) NOT NULL,
FirstName VARCHAR(75) NOT NULL,
JobTitle VARCHAR(50) NOT NULL,
BirthDate DATE NOT NULL,
HireDate DATE NOT NULL,
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
 PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (ID),
INDEX IX_LastName_FirstName (LastName,FirstName),

)
WITH (SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = dbo.EmployeeHistory,
     HISTORY_RETENTION_PERIOD = 6 MONTHS)
    );

ALTER TABLE dbo.Employee ADD CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME() FOR SysStartTime;
ALTER TABLE dbo.Employee ADD CONSTRAINT DF_SysEndTime DEFAULT SYSUTCDATETIME() FOR SysEndTime;

Other options for handling data retention include stretch tables, which place the entire history table, or part of it, in Azure. Partitioning the history table an writing your own custom cleanup script are also options.

Next Steps To Take

  1. Look here for a list of other considerations and limitations.
  2. Think about the tables in your environment where this feature could be useful and do some experimenting.
  3. Talk to your business personnel about this feature and ask them if they know of tables where knowing how the data has changed over time would be helpful. In some cases, you might find that the business is sometimes asked questions about how or when their data changed. This feature is perfect for answering that question.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

How Do I Measure My DBA Skills Part 8

Senior Development DBA

You’ve now likely reached the last stop in your career as a Development DBA. You have arrived at the Senior level. What skills do you have or should you be working on as a Senior Development DBA? I’m glad you asked. Let’s find out.

Senior Development  DBA – 7+ years of experience

    1. Most competencies from the previous levels.
    2. Advanced analysis of SQL Server Performance issues using advanced techniques to increase performance and/or stability. This could involve server level monitoring and analysis to identify issues or concerns.
    3. Makes decisions regarding SQL Server architecture, development strategies and overarching performance considerations, often in tandem with Development team or even the Infrastructure team.
    4. Regularly participates in code reviews of mid-level to Senior Devs and other DBAs, offering best practices and guidance and may assist in implementation.
    5. Fosters and protects healthy development environment, including Source control, controlled or automated migrations, and advanced development/QA Data curation via data masking technologies and potentially other methods.
    6. Makes significant contributions to the design of migrations and upgrades for SQL Server and may provide assistance to other, more junior members of the team, when it comes to implementation.
    7. Leads knowledge sharing in four or more areas.
    8. May contain competencies from Production DBA levels up to level 2 or 3.
    9. Regularly demonstrates good self-awareness, self-management, social awareness and relationship management skills (emotional intelligence

Automating SQL Server Processes

 

automation example

Remember the washing machine example from an earlier post? You wouldn’t want to use a tub and washboard to clean your clothes. You use a washing machine right? It’s faster and more efficient than doing things manually. Yeah, you’re going to be building a lot of washing machines at this stage of your career.

You will be guiding and contributing to efforts to automate processes. This will certainly show up in work efforts for migrations and upgrades as well because the Senior Development DBA will be a person who automates as much as possible. You may be doing this exclusively through PowerShell but you may also be using a variety of DevOps software to automate processes as well.

At this level, you will also be guiding implementation of and answering questions related to things like Dynamic Data Masking, cell level encryption and row-level security. You may be doing advanced data analysis using temporal tables. You will certainly be deciding which of these technologies, and others, should be used and when.

There is also likely going to be some skill overlap with the production DBA. This Senior level person is likely going to need to know quite a few things about production support and troubleshooting. You’re going to need to be able to support SQL Server Agent jobs and be able to troubleshoot job failures. Using a third party monitoring software will likely be needed as well. Many of the skills discussed in earlier posts regarding production DBA skills up level 2 or 3 will be needed at this level.

As with the Senior Production DBA, knowledge sharing and emotional intelligence will be key at this point in your career. You have to be able to work with others, have patience and keep your cool when things don’t go well.

Next Steps to Take

  1. Check out DevOps software that helps you automate tasks.
  2. Check out migration tools like the Database Migration Assistant.
  3. Get comfortable sharing your knowledge with other people. You’re going to need presentation skills and the commensurate people skills that go along with that.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.