Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Enable SQL Server system versioning for SQL tables generated by SQList

In this article, we are going to see how to add SQL Server System Versioning to SQL tables created by SQList.

If you are not 100% familiar with this topic, we recommend that you read this article before proceeding:



Important: do not confuse SQL Server system versioning with SharePoint versioning:

  • SQL Server system versioning creates a version of the row every time the row is updated in the SQL table, either by SQList or any other code;
  • SharePoint versioning creates a version of a list item every time the item is changed in SharePoint via the UI, APIs, or web services.
While the two may match in many cases, that is not guaranteed because several changes could be applied to a SharePoint item between SQList cycles in which case SharePoint would all changes but the SQL database only the last one.


We are going to add system versioning to the tables created by SQList from three SharePoint Online lists:

  1. Categories: an out-of-the-box basic list used as a lookup;
  2. Tags: an out-of-the-box basic list used as a lookup;
  3. Products: a simple list to which a few columns were added, including a single-lookup to the Categories list and a multi-lookup to the Tags list, as well as attachments.
Once exported to a SQL database using SQList, we get 5 tables: one each for the Categories and Tags lists, and three for the Products list (one for the list data, one for the attachments, and one for the multiple categories).

For this exercise, we will be to focusing on the tables related to the Products list as it has the most complex structure amongst the three.



First of all, let's take a look at the structure of the tables.


One pre-requisite, to enabling system versioning to a table, is that it must have a primary key. We can see that the Demo:Products.Attachments table does not contain one; we will see further down how to fix that.

Demo:Products




Demo:Products.AssignedTags




Demo:Products.Attachments

Note the absence of a primary key.


These are the steps to add system versioning to existing SQL tables


For each table to which we want to add system versioning, we need to:

  1. add two columns that will store the period of validity for each row;
  2. define a period for the table based on the two columns above;
  3. if there isn't one already, add a primary key;
  4. enable versioning and define the name of the history table.
We can achieve all that using some fairly simple TSQL.

The TSQL


This is the TSQL that adds versioning to our three tables.


alter table dbo.[Demo:Products]
add
SysStartTime datetime2 generated always as row start not null default getutcdate(),
SysEndTime  datetime2 generated always as row end   not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
period for system_time (SysStartTime, SysEndTime);

alter table dbo.[Demo:Products]
set (system_versioning = on (HISTORY_TABLE = dbo.[Demo:Products_History]));

alter table dbo.[Demo:Products.AssignedTags]
  add
    SysStartTime datetime2 generated always as row start not null default getutcdate(),
    SysEndTime   datetime2 generated always as row end   not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
    period for system_time (SysStartTime, SysEndTime);

alter table dbo.[Demo:Products.AssignedTags]
  set (system_versioning = on (HISTORY_TABLE = dbo.[Demo:Products.AssignedTags_History]));

alter table dbo.[Demo:Products.Attachments]
add
SysPrimaryKey int not null identity(1,1),
SysStartTime datetime2 generated always as row start not null default getutcdate(),
SysEndTime   datetime2 generated always as row end   not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
period for system_time (SysStartTime, SysEndTime);

alter table dbo.[Demo:Products.Attachments]
add
constraint [PK_Demo:Products.Attachments] primary key ([SysPrimaryKey]);

alter table dbo.[Demo:Products.Attachments]
set (system_versioning = on (HISTORY_TABLE = dbo.[Demo:Products.Attachments_History]));


Note: the primary key (SysPrimaryKey) added to the Attachments table, as that table didn't have one.

After running all the TSQL above, we can see that system versioning has been added to the three tables as illustrated here:




Querying the data

That is it, from now on, every update to those tables will create a version in the corresponding history table. For example, we updated the Product ID = 1 a few times, and this is the result:



You can find more information on how to query temporal tables in this article:


Finally, an important setting to configure in SQList

Once you have added system versioning, you must ensure that you configure SQList not to drop the columns you have just added. You have to do this because SQList keeps the structure of the SQL tables in sync with that one of the SharePoint lists, therefore it will try and drop those new columns unless you instruct it not to.

In the Settings tab, there is a text box named "Never drop these columns"; enter the names of the columns you want to keep in this box, comma delimited. Note that by default SQList is already configured to keep the columns used in the TSQL above.
If you have used different names, ensure that you enter them in this box.

For SQList version 6: this setting is already configured by default:



For SQList version 5:




Note: if you need to drop a system-versioned table, you will need to remove system versioning for it first; to do that, see this article: