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.

LEN and DATALENGTH

LEN function is very commonly used function in t-sql. One thing to note in this function that is ignores the trailing blanks;

Declare @v VarChar(5)

Set @v = 'ati '

Select Len(@v)

Same goes for CHAR and NVARCHAR.

To overcome this, use and character at the end and minus 1 from the length J

Declare @v VarChar(5)

Set @v = 'ati '

Set @v = @v + '?'

Select Len(@v)-1

Some people might use DATALENGTH function;

Declare @v VarChar(5)

Set @v = 'ati '

Select  DATALENGTH(@v)

But be alert as DATALENGTH function counts the bytes, not the length. If you change the data type of @v from varchar to nvarchar, the result will change;

Declare @v NVarChar(5)

Set @v = 'ati '

Select  DATALENGTH(@v)

Thanks for reading.

Columnstore Indexes

This is the new feature added in SQL Server 2012 to overcome query performance and index data compression issues. The data stored in the columnstore index is pretty much compressed and very optimized. In SQL Srever 2012, if you have a columnstore index, you cannot perform the DML operations on the table i.e. you cannot insert / update or delete any record. The purpose of this index is for Data warehousing. You have to devise the logic in order to populate the data, for example table partitioning. But this issue is resolved in SQL Server 2014. But there, you will have to create a CLUSTERED COLUMNSTORE INDEX.

You can add this index simply by right clicking the Index folder under the table on which you want to add the column store index;

On clicking the option, a screen will open asking to add the column(s) on which you want to create a column store index (FirstName, LastName).

Leave rest of the properties to default. Click OK button to add the index.

Now, question is how the data will be stored? We have two types of data storge in sql server, RowStore and Column Store. Assume you have a tabel as

RNO       NAME          FNAME
1              Atif                Sheikh
2              Asif                Ahmed
3             Imran              Khan

In RowStore index, the data will be saved as;

Atif,Sheikh
Asif,Ahmed
Imran,Khan

The above data will be saved as a row in the 8k page.

And this will go on until the 8k page is complete.

In case of Columnstore index, the data will be stored as a segment of its respective column. Each segment have only one column. This goes for all the columns in the index. A Column can span on multiple segments and a segment may have multiple data pages. As for above example the data will be stored as;

Col1               Col2

Atif                  Sheikh
Asif                 Ahmed
Imran              Khan

These columns are just like pointers in the 8k page of the index. One page can have multiple columns

Now if you will query with NAME or FNAME in your WHERE condition, the data search and retrieval will be very fast as each condition will have to check one single column. You can check this with Statistics and Execution plans on you tables.

Inline Table Value Function V/S Views

ITVF can be parameterized but views are not. If you need a subset of rows from a view, you need to apply a search criteria in a WHERE clause, but an ITVF can accept search criteria as function parameters.

When quering two tables joined together, the implementation can use the parameter in the JOIN condition while using ITVF. But in views, you need to add it in the Where condition.

Suppose you need to get all titles written by authors whose last name is stored in variable @author. Here's the code using the view:

SELECT a.*, t.titleid

FROM viewauthors a

JOIN titleauthor t ON a.auid = t.auid

WHERE a.au_lname = @author

In case of ITVF, you can use it as;

SELECT a.*, t.titleid

FROM dbo.itvfauthors( @author ) a

JOIN titleauthor t ON a.auid = t.auid

As you can see, you do not need a WHERE condition and the reslt set iof ITVF is filtered via parameter @author.

Poison Message in Services Broker

A poison message in Services Broker is message that cannot be processed by your code/activation stored procedure causing your code/activation stored procedure to rollback. In these circumstances the message is returned back to the queue. Unfortunately, these messages will continue to be picked up by your code/activation stored procedure resulting in a rolled back transaction and the message is returned back to queue. In other words, a poison message is an invalid message.

Here are some common scenarios which create poison messages:

·         A message is violating foreign keyunique constraints or check constraints

·         A message trying to insert a NULL values into a column (NOT NULL) that does not accept NULLs

·         A message that attempts to insert an incompatible value into column

·         Any data which causes your activation stored procedure to rollback

Unfortunately there is no built-in mechanism to handle (delete) poison messages. You need to write custom code to manage these records. A simple way to approach this is in your activation stored procedure instead of rolling back the transaction in CATCH block of your TRY...CATCH error handling check the record to see if the rollback was due to a poison message. If yes, then log this message in a dedicated error queue. Then as a portion of your business process review these records to identify all the offending/poison messages which caused the control go to CATCH block.

Another approach is to subscribe to the Broker:Queue Disabled trace event or BROKERQUEUEDISABLED event which gets raised when a queue gets disabled after five consecutive rollbacks. On occurrence of such event, you need to receive each message from the queue. If the message is correct, then rollback the transaction so that it returns back to queue for actual processing or if it is a poison message, log it to an exceptions log for auditing purposes and commit the transaction to remove the poison messages from your queue.

Insert File in SQL Server table

INSERT INTO dbo.Files (FileName, [File])

SELECT 'MyDoc.doc' AS FileName, *

FROM OPENROWSET(BULK N'C:\MyDoc.doc', SINGLE_BLOB) AS [File]

A simple XML parsing example

DECLARE @t TABLE ( Id INT PRIMARY KEY, booksXML XML )

INSERT INTO @t VALUES

( 1, '<books
category="novel"><book>Gone with the
wind</book><book>The lord of the rings</book></books>'
),

( 2, '<books
category="textbook"><book>linear
algebra</book><book>advanced
mathematics</book></books>' )

SELECT t.Id, x.y.value('.', 'VARCHAR(100)') book

FROM @t t

CROSS APPLY t.booksXML.nodes('books/book') x(y)

Find data difference between two schema identical tables

Declare @vSQL varchar(max)

Declare @vCols varchar(max)

Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)

Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)

Insert into vTable1

Select 1,123,'CS',465 Union All

Select 2,123,'CS',345 Union All

Select 3,223,'TE',190

Insert into vTable2

Select 1,123,'CS',465 Union All

Select 2,223,'TE',345 Union All

Select 3,223,'TE',190

-- Get the column names from schema with case statements to get 0 or 1 as
result

-- Now, this will depend upon the columns of your actual tables

Select @vCols = Stuff((Select ',case when a.' +
[name] + ' = b.'
+ [name] + ' then 1 else 0 end as ' +
[name] from sys.columns
where Object_id
= Object_id('vTable1') for XML Path('')),1,1,'')

print @vCols

-- Concatenate the @vCols with main sql

Set @vSQL = ' Select a.id,' + @vCols + ' From vTable1 a

Inner Join vTable2 b on b.ID = a.ID '

Print @vSQL

Exec (@vSQL)

 

Drop table vTable1

Drop table vTable2

Check / evaluate Multiple LIKES without Dynamic SQL

CREATE TABLE vTable (id INT, NAME VARCHAR(100))

INSERT INTO vTable

SELECT 1,'Shamas Qamar' UNION ALL

SELECT 2,'Atif' UNION ALL

SELECT 3,'Kashif' UNION ALL

SELECT 4,'Imran'

DECLARE @vParam VARCHAR(100)

SET @vParam = 'Sha,hif' 

SELECT * FROM vTable

CROSS APPLY (SELECT [value] FROM
dbo.fnSplit(@vParam,',')) b

WHERE NAME LIKE '%' + b.[VALUE] + '%'

DROP TABLE vTable