• Adam Thurgar

SQL Server 2016 Temporal tables

Whilst trying to create a temporal table (system versioned table) on SQL Server 2016, I kept getting a syntax error on the SET (SYSTEM_VERSIONING = ON) syntax stating that is was not supported.

After no success with changing the create table command, what I had to do was create the table without the system versioning on and then go and alter the table and set the system_versioning on and I also specified the History_Table.

If you want to alter or drop (or truncate) your system versioned table you will need to SET (SYSTEM_VERSIONING = OFF), make your changes and then turn it back on again.

I also added default constraints to the ValidFrom and ValidTo date fields and made these HIDDEN columns.

Recent Posts

See All

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is

Autoclose and autoshrink - set them to off

By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as p

Hypothetical indexes

We had a query recently about a database that had a lot of objects starting with the name _dta_. We explained that these where hypothetical indexes and statistics that had been created by the Database