October 21, 2016 at 4:16 am
Another question: what's gonna happen if I change a column data type from, say, varchar(50) to nvarchar(100)?
Or add a new column in between 2 existing ones?
_____________
Code for TallyGenerator
October 21, 2016 at 4:33 am
It is not a problem to change datatype from length 50 to 100. But it can be a problem to change from 100 to 50 if a row has a datalengt greater than 50.
It is not possible to add a column between 2 other columns in SQL Server. A new column will alweays be the last column in the table. SSMS can create a script, in the way that a new column can be placed between 2 other columns. This is done by creating a new table, copy data, ..., rename the tables, drop the old table, ... But this is not possible when the table is a system_version table.
Carsten
October 21, 2016 at 4:54 am
csj (10/21/2016)
It is not a problem to change datatype from length 50 to 100. But it can be a problem to change from 100 to 50 if a row has a datalengt greater than 50.It is not possible to add a column between 2 other columns in SQL Server. A new column will alweays be the last column in the table. SSMS can create a script, in the way that a new column can be placed between 2 other columns. This is done by creating a new table, copy data, ..., rename the tables, drop the old table, ... But this is not possible when the table is a system_version table.
Carsten
My question was not so much about length, but about varchar - nvarchar change.
You're right, if I do it in SSMS then the table gets dropped at some stage.
If it's allowed then the whole history will be lost.
If not - then any changes to table design, apart from adding/dropping the last column, are prohibited for version controlled tables.
_____________
Code for TallyGenerator
October 21, 2016 at 5:07 am
The same rules as for other tables!!! If it is possible - and it will always be possible to alter from VARCHAR(50) to NVARCHAR(100) - then it's also allowed for system_version'ed tables.
In SQL Server a new column will always be added as the last column in the table, if you use ALTER TABLE ... ADD COLUMN. In other SQL-based databasesystems it is possible to specify that the new column should be placed before or after another column - but not in SQL Server.
November 22, 2016 at 9:19 am
deleteme
September 4, 2017 at 1:57 am
Hi,
what i am missing in this feature : would like to exclude columns from versioning , e.g. "last updated" when nothing changes.
question: what is that WAITFOR DELAY '00:00:02'; for ?
best regards herbert
September 4, 2017 at 2:11 am
h.tobisch - Monday, September 4, 2017 1:57 AMHi,
what i am missing in this feature : would like to exclude columns from versioning , e.g. "last updated" when nothing changes.question: what is that WAITFOR DELAY '00:00:02'; for ?
best regards herbert
Hi,
There is currently no way to exclude columns from versioning and I don't think Microsoft is planning to implement it.
The WAITFOR DELAY '00:00:02' waits two seconds and is to make sure there is a time gap between the operations.
Best Regards
Johan
September 5, 2017 at 5:21 am
this is funny 🙂
some time ago, about 6 or 7 years back, I was working on the project where we implement similar feature to version control the data. and in very similar way.
each table where we needed to have historical data, has 2 columns at the end of the table
StartDate and EndDate. StartDate was filled in when record was inserted. and EndDate was filled
when record was deleted AKA "Soft Delete"
when ever we needed to change some info in the record that required history keeping, the process was like , copy the record -> date stamp enddate in the original record -> change the new record.
we also had some tables where additional columns kept the EditDate and editByUserID info.
so we can see who changed the record and when.
later on, we also had implemented the data retention feature ,again similar to temporal tables, where a trigger on the table would store any changes to data in a history table.
as in, what record, what column(s) changed , the old value and new value and userID.
not sure how flexible this new functionality is, but it is a good option to have.
September 5, 2017 at 7:16 am
This is a great article and I have always struggled with temporal data, every time needing a special case. Unfortunately for me, I am not yet using SQL 2016 and some of its great feature. We split data into operational, short term historical and archival. Archival data is data older than say 12 months and usually reside on a different server with very high storage capacity, yet with slower performance. I have three questions:
1. Does SQL 2016 has any built-in mechanism for the background process that cleans the historical temporal table to move the data to the archival database on a different server?
2. Can the retention period be parameterized to provide elasticity and configurability to the data retention?
3. What is the performance impact on having the historical table? Is the time of data move from the temporal (main) table to historical table configurable? How about the time the clean up of the historical table? How often and what is its performance impact on the overall CPU and memory usage?
September 5, 2017 at 7:27 am
N_Muller - Tuesday, September 5, 2017 7:16 AMThis is a great article and I have always struggled with temporal data, every time needing a special case. Unfortunately for me, I am not yet using SQL 2016 and some of its great feature. We split data into operational, short term historical and archival. Archival data is data older than say 12 months and usually reside on a different server with very high storage capacity, yet with slower performance. I have three questions:1. Does SQL 2016 has any built-in mechanism for the background process that cleans the historical temporal table to move the data to the archival database on a different server?
2. Can the retention period be parameterized to provide elasticity and configurability to the data retention?
3. What is the performance impact on having the historical table? Is the time of data move from the temporal (main) table to historical table configurable? How about the time the clean up of the historical table? How often and what is its performance impact on the overall CPU and memory usage?
Glad you enjoyed the article!
1. No. Only Azure SQL Databases have a built-in cleaning mechanism.
2. No.
3. Performance is very good (from my own experience). Data always moves immediately from the main table to the historical table. Cleaning up the historical table doesn't have high performance impact if you do it right. I haven't had any problems with it.
September 5, 2017 at 7:33 am
N_Muller - Tuesday, September 5, 2017 7:16 AMThis is a great article and I have always struggled with temporal data, every time needing a special case. Unfortunately for me, I am not yet using SQL 2016 and some of its great feature. We split data into operational, short term historical and archival. Archival data is data older than say 12 months and usually reside on a different server with very high storage capacity, yet with slower performance. I have three questions:1. Does SQL 2016 has any built-in mechanism for the background process that cleans the historical temporal table to move the data to the archival database on a different server?
2. Can the retention period be parameterized to provide elasticity and configurability to the data retention?
3. What is the performance impact on having the historical table? Is the time of data move from the temporal (main) table to historical table configurable? How about the time the clean up of the historical table? How often and what is its performance impact on the overall CPU and memory usage?
2016 would offer Stretch DB into Azure and works perfectly for the historical tables that temporals create / use.
in my testing, the performance impact for enabling temporal tables is minimal.
as far as configurations, it is basically on / off. also, the history table is not required. you can enable the ValidFrom and ValidTo columns independently of the history table.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply