Concept and basics of Temporal tables in SQL Server 2016


In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

  • Introduction
  • What is a temporal table?
  • Why Temporal table?
  • How does temporal table work?
  • Consideration and limitation
  • Temporal tables vs CDC
  • Creation and configuration
  • Clean up and removal
  • References

Introduction

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

To complete this artcile please check it here in SQLShack 

SQL SERVER 2016 Always Encrypted


It is the new way of Data encryption introduced with SQL Server 2016 used for encrypting the sensitive date encrypted at the application layer via ADO.NET. This means you can encrypt your confidential data with the yours.NET application before the data being sent across the network to SQL Server.

Column master key:

The Column Master Key is stored on an application machine, in an external key store. This key used for protecting the column encryption key and SQL Server doesn’t have any access to this core directly

Column Encryption Key:

But this one is stored in SQL Server and it used for encrypting/decrypt the Always Encrypted column at this time the scenario of the encryption will be the first ADO.NET has decrypted the Column Encryption Key, using the Column Master Key then SQL Server use Encryption Key for encrypting/decrypt the Always Encrypted column.

1

Technical Demo:

  • Expand your DB under security you will find “Always Encrypted Keys
  • Right click create new column master key

USE [SQL2016DEMO]
CREATE COLUMN MASTER KEY [Demo_Always_Encrypted_CMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
KEY_PATH = N’CurrentUser/My/09D607EDCEC14A9E009FC59B67E7F423DBEE9C9E’
)

Continue reading “SQL SERVER 2016 Always Encrypted”

WordPress Annual Report to my SQL Server Community


I completed 4 Years as SQL Server blogger with my small Community SQL Server Consultation Community (http://mostafaelmasry.com)

I started it from 2012 and I will not stop sharing the information ad the new Technology in SQL Server

You can check the WordPress annual Report for the last 4 years from here

 

WordPress Annual Report to my SQL Server Community

 

SQL Server 2016 Community Technology Preview 3.1 is available


Configurations for all Microsoft SQL Server 2016 followers and for the guys are interested to know more and more about this new technology Microsoft released version no 3.1 CTP from SQL Server 2016 and it released new improvement in this version (In-Memory OLTP) i will explain it later in detail but now for more information and for downloading this new version you can check it from Here

Also to cover all the new updates in SQL Server 2016 you can check it from Here

To know More about new feature in SQL Server 2016 you can check it from HERE

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS ,Facebook WHO WE ARE

 

 

Live Query Statistics in SQL Server 2016


Today we have very good features it will help us in our daily work (Performance troubleshooting and for the debugging of queries) as we know to tune any SQL Server query you should Check first the Execution plan to decide from where the problem from SQL Server 2005 to SQL Server 2014 we have two types of Execution plan:

  • Estimated Execution Plan: Giving you an idea of how SQL Server will most likely perform query execution
  • Actual Execution Plan       : This will show you things that might hint at “out-of-date” statistics etc. But to get this, you must run the query – which can take a long time.

So before if we have case one Query returned Millions of record and we need to check the Query execution plan for it we will go directly for the Estimated Execution plan because actual Execution plan if we need to see it we will wait more time to the query finish and return all the result but Now in SQL Server 2016 we have new Execution plan type ” Live Query Statistics ”  this new features it will he;p us to see the Execution plan when the Query running and the Effect of the Query on the Execution plan step by step from more points:

  • Execution Query Percentage
  • Actual Number of Rows
  • elapsed time
  • operator progress

What I use here in my demo is simple Query return more than 8 Millions of records if I wait for this query to return all this data to see the Actual Execution plan I will wait around 20 Minutes but now in SQL Server 2016 I will See the Execution plan the Actual Execution Plan Live with Live Query Statistics 

SELECT *
FROM sys.all_columns tmp1
Cross JOIN sys.all_columns tmp2

Live Query3

Live Query1 Live Query2

To know More about new feature in SQL Server 2016 Keep following us and to check the previous posts in SQL Server 20116 you can check it from HERE