Constructing a SQL Server 2016 temporal table using SMO

Temporal tables are arguably one of the coolest new features in 2016 that will finally put the nail into the coffin of homemade change tracking with triggers. Today I had to build one of these tables programmatically using SMO (SQL Server Management Objects) and could not find an existing demo; so I made one!

First create a database on a CTP 3.2 instance.

Create Database Temporal

Start up PowerShell. I'll make the rest of my comments in the code so you can cut and paste the entire sample at once.

# Fill in your server details.
$serverInstance = "."
$databaseName = "Temporal"
$schemaName = "dbo"
$tableName = "Test"

# This clean-up allows you to rerun the demo later. Don't leave this in 
# production code; it's prone to injection.
Import-Module SQLPS -DisableNameChecking
Invoke-SqlCmd -ServerInstance $serverInstance -Database $databasename -Query "
    If Exists (Select Top 1 0 From sys.tables Where Schema_Name(schema_id) = '$schemaName' And name = '$tableName') 
    Begin 
        Alter Table [$SchemaName].[$tableName] Set (System_Versioning = Off)
        Drop Table [$SchemaName].[$tableName]
    End"

# Connect to the server and database. This is easier; I haven't used SMO 
# to construct tables entirely offline.
$sqlConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverInstance)
$sqlConnection.Connect()
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)
$sqlDatabase = $sqlServer.Databases[$databaseName]

# Begin preparing a new table. It's good practice to call Refresh because 
# if the table already exists this can pre-populate the existing schema.
$newTable = New-Object Microsoft.SqlServer.Management.Smo.Table($sqlDatabase, $tableName, $schemaName)
$newTable.Refresh() 

# We require a primary key. We'll use an Identity column.
$dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType("Int")
$identityColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, "Id", $dataType)
$identityColumn.Nullable = $false
$identityColumn.Identity = $true
$identityColumn.IdentitySeed = 1
$newTable.Columns.Add($identityColumn)

$primaryKey = New-Object Microsoft.SqlServer.Management.Smo.Index($newTable, "PK_$tableName")
$primaryKey.IndexType = [Microsoft.SqlServer.Management.Smo.IndexType]::ClusteredIndex
$primaryKey.IndexKeyType = [Microsoft.SqlServer.Management.Smo.IndexKeyType]::DriPrimaryKey
$primaryKey.IndexedColumns.Add((New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn($primaryKey, "Id")))
$newTable.Indexes.Add($primaryKey)

# And a column to store some random text to prove history is working.
$dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType("NvarChar")
$dataType.MaximumLength = 100
$dataColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, "Data", $dataType)
$dataColumn.Nullable = $false
$newTable.Columns.Add($dataColumn)

# Now we fulfil temporal requirements; from and to dates.
$dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType("DateTime2", 2)

$fromColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, "_ValidFrom", $dataType)
$fromColumn.Nullable = $false
$fromColumn.IsHidden = $true                
$fromColumn.GeneratedAlwaysType = "AsRowStart"
$newTable.Columns.Add($fromColumn)

$toColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, "_ValidTo", $dataType)
$toColumn.Nullable = $false
$toColumn.IsHidden = $true
$toColumn.GeneratedAlwaysType = "AsRowEnd"
$newTable.Columns.Add($toColumn)

# The intent of the last boolean is not documented, but specifying true 
# creates the period link (which is required).
$newTable.AddPeriodForSystemTime("_ValidFrom", "_ValidTo", $true)

# Optionally we can name our history table.
$newTable.HistoryTableSchema = $SchemaName
$newTable.HistoryTableName = "$($tableName)_History"
$newTable.IsSystemVersioned = $true

# And finally, show us the script and create it.
$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptingOptions.Indexes = $true

try {
    $newTable.Script($scriptingOptions)
    $newTable.Create()
} catch {
    $_ | Select *
}

Your output will look like so:

CREATE TABLE [dbo].[Test](
	[Id] [int] IDENTITY NOT NULL,
	[Data] [nvarchar](100) NOT NULL,
	[_ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	[_ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[Id]
),
	PERIOD FOR SYSTEM_TIME ([_ValidFrom], [_ValidTo])
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Test_History] , DATA_CONSISTENCY_CHECK = ON )
)

Why would you want to do this? In my case I have a PowerShell function to accept an input object, convert it to a DataSet (a .NET representation of tables and the links between them), and bulk copy the data into SQL Server. However because the input objects can vary slightly with additional tables or additional columns I use the flexibility of SMO to create the tables and columns on the fly.

Let's take the demo for a spin and confirm it's working.

Use Temporal

Insert	dbo.Test ([Data]) 
Values	('My first insert')
Insert	dbo.Test ([Data]) 
Values	('My second insert')

Select	'This is the current data' As Information,
		*
From	dbo.Test

Waitfor Delay '00:00:05'

Update	dbo.Test
Set		[Data] = 'Overwritten!'

Select	'Oops' As Information,
		*
From	dbo.Test

Declare	@TwoSecondsAgo Datetime2 = Dateadd(Second, -2, Sysutcdatetime())

Select	'Temporal table to the rescue' As Information,
		*
From	dbo.Test
For		System_Time As Of @TwoSecondsAgo

Results:

I have one more tip. If you're ever experimenting with tables in SMO and see exceptions that complain about a missing column:

PSMessageDetails      : 
Exception             : System.Management.Automation.MethodInvocationException: Exception calling "Create" with "0" argument(s): 
                        "Create failed for Table 'dbo.Test'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: 
                        Create failed for Table 'dbo.Test'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An 
                        exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: 
                        Column name '[Id]' does not exist in the target table or view.
                        Could not create constraint or index. See previous errors.
                           at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object 
                        execObject, DataSet fillDataSet, Boolean catchException)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes 
                        executionType)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes 
                        executionType)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, 
                        ExecutionTypes executionType)
                           at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, 
                        ScriptingPreferences sp)
                           at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
                           at CallSite.Target(Closure , CallSite , Object )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, 
                        Exception exception)
                           at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at <ScriptBlock>, C:\Temp\Temporal.ps1: line 77
PipelineIterationInfo : {}

The reason will be that SMO functions accept a mix of actual SMO columns (a data type), and raw strings, and you've mixed some up.

The actual error comes when the column data type is converted to a string; the column name has brackets attached. SMO functions add their own brackets and so the brackets are doubled up, and SMO is unable to find the column by the broken name.

Share on: