System versioned Tables in SQL Server 2016

In SQL Server 2016, System versioned tables are the tables whose data is maintained in the history table. This history is maintained by SQL Server itself. All you need is to specify 2 additional datetime2 columns (SysStartTime and SysEndTime in the example below) and a clause with these two columns as : PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

To create a system versioned table;

------ System versioned table

CREATE TABLE Department  

(   

     DeptID int NOT NULL PRIMARY KEY CLUSTERED 

   , DeptName varchar(50) NOT NULL 

   , ManagerID INT  NULL 

   , ParentDeptID int NULL 

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON)  

;

The system versioned table creates a temporal table which maintains the history of the data. By default, the name is as MSSQL_TemporalHistoryFor_1541580530. But you can specify the name of this table in the CREATE TABLE statement above;

------ System versioned table with name the temporal table

CREATE TABLE Department  

(   

     DeptID int NOT NULL PRIMARY KEY CLUSTERED 

   , DeptName varchar(50) NOT NULL 

   , ManagerID INT  NULL 

   , ParentDeptID int NULL 

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.DepartmentHistory ) )  

;

In above example, dbo.DepartmentHistory is created as temporal table.

In the management studio, the system versioned tables are shown as below;

The above diagram shows the default name of the temporal table. If you have specified the name of your temporal table (dbo.DepartmentHistory in our example), then it will be shown;

You can also rename the temporal table. In sys.objects, these tables are listed as a user_table. To check if any table have a temporal table or not you can query the “OBJECTPROPERTY” for property “TableTemporalType”;

Select OBJECTPROPERTY(OBJECT_ID(N'dbo.Department', N'U'), N'TableTemporalType')

INSERT / UPDATE / DELETE

When we insert any data in the main table (dbo.Department), no data is inserted in the history / temporal table. In case of UPDATE you will see the latest data in main table and the history data in the temporal. The SysStartTime and SysEndTime are updated as per the time of UPDATE statement;

Update [dbo].[Department]

Set DeptName = 'A'

where DeptID = 3

Select * from [dbo].[Department]

Select * from [dbo].[DepartmentHistory]

You can see above that DeptName for DeptID=3 is changed to ‘A’ and the old value ‘D3’ is available in the temporal table. Please note that the copy of the complete record is maintained in the temporal table.

You cannot delete the data directly from Temporal table. When you delete data from main table, the data is stored in temporal table and deleted from the main table.

Delete from [dbo].[Department] where DeptID = 3

Select * from [dbo].[Department]

As you can see the data is deleted from main table. But the record is available in temporal table;

Select * from [dbo].[DepartmentHistory]

When you try to delete from the DepartmentHistory temporal table, it will generate error;

Delete from [dbo].[DepartmentHistory] where DeptID = 3

So, you cannot delete data from temporal table.

WHAT ELSE YOU CANNOT DO

TRUNCATE TABLE

1.       You also cannot TRUNCATE the system versioned and the temporal table. You will get the following error;

Msg 13545, Level 16, State 1, Line 35

Truncate failed on table 'Test16.dbo.Department' because it is not supported operation on system-versioned tables.

2.       You cannot change the schema of the temporal table.

3.       You cannot add trigger to temporal table

YOU CAN ADD INDEX

You can index the temporal table and the main table as per your requirement.

XACT_ABORT V/S TRY .. CATCH

Few days back we witnessed an issue that the SQL Server was running slow. We investigated and soon found out that there were uncommitted transactions on the server. We re-confirmed those transactions from the developers and resolved the issue. Someone tossed the idea to apply SET  XACT_ABORT ON in all SPs so that this situation may not occur again. That made me to write this article so that I can explain the pros and cons of SET XACT_ABORT ON option.

SET XACT_ABORT ON

As many of you already know that this options just takes the whole batch a one transaction. If any of the statement in the batch fails, the transaction of the whole batch is rolled back. Commits in case of success. Simple. But not that simple. You cannot control the flow of transaction with if XACT_ABORT is ON. TRY .. CATCH will not work. Even if you have the error log is implemented in CATCH block, it will not be saved as it will also get rolled backed. So, DB end error log is gone. In some cases you may need to continue with the rest of your code in case of an error, for example return a dataset. You cannot do this as well. The plus point you will never have an open transaction on your server. All transactions will either commit or rollback.

Another way to handle an open transaction issue is to apply TRY .. CATCH in all SPs. Ok, I know. There are few limitations of TRY .. CATCH. TRY .. CATCH do not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction in case of a schema error (table does not exists, etc ). Check the code below;

       BEGIN TRY 

              -- Table does not exist; object name resolution 

              -- error not caught. 

              begin Tran

              SELECT * FROM NonexistingTable;

              commit 

       END TRY 

       BEGIN CATCH 

              rollback

              SELECT  

                     ERROR_NUMBER() AS ErrorNumber 

                 ,ERROR_MESSAGE() AS ErrorMessage; 

       END CATCH 

      

       GO    

       Select @@TRANCOUNT

      

Execute the above statements. You will end up with an opened transaction.

You can also try this in a stored procedure. You will still end up in an open transaction.

Bow add SET XACT_ABORT ON;

       SET XACT_ABORT ON

       BEGIN TRY 

              -- Table does not exist; object name resolution 

              -- error not caught. 

              begin Tran

              SELECT * FROM NonexistingTable;

              commit 

       END TRY 

       BEGIN CATCH 

              rollback

              SELECT  

                     ERROR_NUMBER() AS ErrorNumber 

                 ,ERROR_MESSAGE() AS ErrorMessage; 

       END CATCH 

      

       GO    

       Select @@TRANCOUNT

Your transaction will be rolled back and you will find no open transaction.

CONCLUSION

XACT_ABORT ON solves the open transaction issue but at the cost of logging implemented in CATCH block (if you have any).

But “NonexistingTable” type of issues should not be in the production environment. Like why will I deploy a SP using a table that does not exists? Strange thing for me. You should have QA in place and your deployment scripts should be verified before running in the production environment.

 You decide.

SET DATEFIRST and SET LANGUAGE

This is a tricky part. SQL Server have language settings at the following levels;

1.       Server

2.       User Login

3.       Session

You need to be very clear for the language settings of the user / login as it may impact the date functions;

DECLARE @Today DATETIME; 

SET @Today = getdate(); 

 

SET LANGUAGE Italian; 

SELECT DATENAME(month, @Today) AS 'Month Name'; 

Select @@DATEFIRST

 

SET LANGUAGE us_English;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE British;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE us_English;

GO 

As you can see from the above code, I have set the language back to US English (English in SQL Server language drop downs) as I like to have it that way. You also see that @@DATEFIRST function returns different value for different languages. This value is set on the basis of;

1.       Language selected

2.       SET DATEFIRST option

You can change the language and still keep the First date of week same as in US English;

SET LANGUAGE us_English;

Select @@DATEFIRST

SET LANGUAGE British;

Select @@DATEFIRST

SET DATEFIRST 7

Select @@DATEFIRST

As you can see, US_English sets the @@DateFirst to 7 and British sets it to 1. But we have used SET DATEFIRST to change the language to British but kept @@DATEFIRST  to 7 using SET DATEFIRST option.

This is very important when you are working multilingual databases. Where ever you are using day of week values (in DatePart, etc), you should keep this change in mind as well. Either use one language throughout your database for Server, users and session. But if there might be any change to get the benefit of SQL Server multilingual support, you should use SET DATEFIRST into account when programming to keep all the calculations and checks aligned.

xp_fileexist And its Alternate

xp_fileexists is a very useful undocumented stored procedure of SQL Server.

The usage of xp_fileexist is as follow;

Exec xp_fileexist “E:\abc.txt”

The values returned are

You can also use OUTPUT parameter to get the value of “File Exists” column as below;

Declare @vFileExists int

exec master.dbo.xp_fileexist 'E:\abc.txt', @vFileExists OUTPUT

Select @vFileExists

If you want to save all three returned values, then you will have to go throu Temp Table approach;

Declare @vFileExists Table (FileExists int, FileDir int, ParentDirExists int)

insert into @vFileExists

       exec master.dbo.xp_fileexist 'E:\abc.txt'

Select * from @vFileExists

Sometimes xp_fileexist behaves abnormally. Your file is there and it does not check the file and returns 0 in FileExists column. This is an extended SP so its behavior may also change with different versions of SQL Server.

If you are facing the same problem, change you code with xp_cmdshell “dir” approach;

Declare @vExistsPath nvarchar(100)

Declare @files Table ([FileName] nvarchar(100))

Set @vExistsPath = ''

Set @vExistsPath = 'E:\abc.txt'

Set @vExistsPath = 'dir ' + @vExistsPath + ' /b'

Insert into @files EXEC xp_cmdshell @vExistsPath

Select * from @files

if Exists(Select 1 from @files where [FileName] = 'abc.txt' And [FileName] is Not Null)

begin

       Select 1

end

else

begin

       Select 0

end

The “/b” switch returns only filename with extension as a result of “dir” command