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:
- Categories: an out-of-the-box basic list used as a lookup;
- Tags: an out-of-the-box basic list used as a lookup;
- 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:
- add two columns that will store the period of validity for each row;
- define a period for the table based on the two columns above;
- if there isn't one already, add a primary key;
- enable versioning and define the name of the history table.
We can achieve all that using some fairly simple 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: