SQL Server 2016 gives support for temporal tables or system-versioned table. It’s a new type of system table which used us to keep the history of data changes. As per the implementation that we are going to have two table one with current values and another with the historical data.

The table should contain two columns as mandatory with the datatype datatime2. These columns used to refer the period. The temporal table will store the data when the update or deletion operation is performed on the parent table.

To create a table with the System versioned enabled

 CREATE TABLE Feedback(Id INT IDENTITY(1,1) PRIMARY KEY,Empid INT, Feedback VARCHAR(500) ,
 FeedbackFrom DATETIME2(2) GENERATED ALWAYS AS ROW  START,
 FeedbackTo DATETIME2(2)  GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME(FeedbackFrom,FeedbackTo)
 )
 WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.Feedback_History))

The System versioned table will consits the same column information as the main table without constraints.

 INSERT INTO Feedback(Empid,Feedback) VALUES(1001,'Testing')
 
 SELECT * FROM Feedback
 SELECT * FROM Feedback_history

To view the table and related system versioned table name.

 SELECT name as 'TableName', OBJECT_NAME(HISTORY_TABLE_ID)as HistoryTable
  FROM SYS.TABLES WHERE HISTORY_TABLE_ID IS NOT NULL

If we need to drop the table, it cannot be done till there is a system versioned table is referred.

DROP TABLE IF EXISTS Feedback

To disable the system system-versioned table

 ALTER TABLE Feedback SET ( SYSTEM_VERSIONING = OFF )

This command will remove the system versioning and makes into two normal tables.

Creating a table with temporal tables these prerequisites is must.

· Primary key

· Two columns with datetime2. One with sysstarttime and sysendtime as not null type. Users are not allowed to update or insert the value. The column name doesn’t contain any restrictions.

· Instead of trigger should not be used.

· FileStream column type not supported.

Temporary table

· It cannot have constraints

· The data cannot be modified.

In the recent SharePoint development changes, I noticed that, most of the customers are moving towards the Client side development. Especially the CSOM and the JSOM. In the same manner, I met with an interesting requirement like, I need to export a HTML table like structure to Export using the Javascript. As I mentioned earlier, nobody wants C# to be written nowadays.

So let us see, how it is going to be.

I have a button like this.

 <button id="btnExport" onclick="fnExcelReport();"> EXPORT </button>

On Click of that button, let me call a method called fnExcelReport().

 function fnExcelReport()
         {
               var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
               var textRange; var j=0;
               tab = document.getElementById('{818AAD80-3CAD-48C6-AABA-D0A225A4D08F}-{70352EC0-82B2-4175-98A2-84A9B95003BA}'); // id of table
 
               for(j = 0 ; j < tab.rows.length ; j++) 
               {     
                     tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
                     //tab_text=tab_text+"</tr>";
               }
 
               tab_text=tab_text+"</table>";
  
 
               var ua = window.navigator.userAgent;
               var msie = ua.indexOf("MSIE "); 
 
               if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv:11./))      // If Internet Explorer
               {
                  txtArea1.document.open("txt/html","replace");
                  txtArea1.document.write(tab_text);
                  txtArea1.document.close();
                  txtArea1.focus(); 
                  sa=txtArea1.document.execCommand("SaveAs",true,"Global View Task.xls");
               }  
               else //other browser not tested on IE 11
                  sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  
                 return (sa);
         }
 

Before that, I need to keep a hidden Iframe anywhere on the document. This requires only for the Internet Explorer. The other browsers don’t require this. Because, the lengthy content cannot be rendered in the IE.

The hidden Iframe is like

 <iframe id="txtArea1" style="display:none"></iframe>

Happy Coding,

Sathish Nadarajan.