Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how the new function SVT of SQL SERVER 2016 cleans up the data. the article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

SQL SERVER is very interesting 2016 to introduce a historical version control function, the meaning of this function is to help data tables record their past operations according to time, which is actually equivalent to

Don't complain about whether there is anything wrong with this feature at the beginning of the design, but we do encounter it, and at the same time, there is a rollback, which causes a problem with the timeline, but the task cannot be executed. Although it is a small amount, this function is actually not rigorous.

How to turn on and use this function, let's not talk about it here. What we want to talk about here is how to clean up the data. Because what is stored is the historical activity of a row, so the amount of its data is generally a match that the data table has frequently modified, that is, N pieces of your previous data.

Let's do a few nonsense first. Let's first understand this function. There are two kinds of historical version control tables when they are built. If they are built into another one, it will cause some trouble to the later automatic data cleaning regularly. So the figure given above is two, one is a given name, and the other is not.

Insert data, modify data, view historical version control tables

As you can see from the above picture, the need for this historical version control

1 must have a primary key, otherwise how can you know a line change and confirm

2 there must be a time field, as well as the start time of the change and the end time of the change, and the start time of the line change is his sysendtime time, and he is consistent with his primary key, and the same time in sysstarttime is sysstarttime. We can see from the picture above.

3 when the historical version control table works with the original table, some operations are prohibited

For example, the operation above.

Now let's get straight to the point. How to clean up these historical versions of data is actually very simple to deal with. The main thing is that when you deal with it, you have to disassociate the historical version control table from the main table and turn it into a table that has nothing to do with the physical table, and then you can "kill" it.

After processing, we mount him with the original table and continue to work for us, but the data changes in the operation of the data table during this period will not be recorded in our historical version control table. And the historical version control table records only the operations of UPDATE and Delete.

However, as mentioned above, if you do not give the table name when you create the history version control table in the course of operation, then every time you do the above operation, as soon as you open and close, your SQL SERVER will change the table name of your history version table. Although there is no problem, it is difficult for you to automatically clean up the data of the history table, so it is recommended to give a name. Reduce the trouble of automatic processing in the future. Or you are.

Alter table XXXX set (system_versioning = on) (history_table = the history version table name you want to set) can also correct the problem that the history version table name was random because you did not set the table name.

Of course, there is also a way to create your own historical archive table, and then mount the historical archive table you have created, but it should be noted that the requirements of clustered and non-clustered indexes (the vernacular is the primary key, and the index of related event fields and primary keys) you have to handle it yourself.

Then it would be very convenient to write a stored procedure to deal with it regularly.

In addition, such a table will be different from an ordinary table in some operations.

For example, to delete a table, you need to undo the relationship between the entity and the historical version control table, and then delete the table separately.

This is how the new SQL SERVER 2016 feature SVT is cleaned up. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.