In this article, we will take a walk-through all fresh SQL 2016 features and cover them one by one.
As the word suggests, 'Always Encrypted' feature of SQL 2016 'Always' keeps your sensitive data 'Encrypted' either at rest (local environment) or at remote (Cloud/Azure). It will help to protect data from people who may play around it like DBAs, Cloud operators, high-privileged but unauthorized users.
How It WorksYou can set Always Encrypted to individual column (where your sensitive data resides). While configuring columns, you need to specify encryption algorithm and cryptographic keys for data protection. There are basically two keys you need to define:
- Encryption Key for column data encryption (It will be used to encrypt data for specific column)
- Master Key: for Encryption of column encryption keys
JSON data will be stored in
NVARCHARtype. Due to
NVARCHARtype, an application has the following benefits:
- Already stored JSON data (as text) can be easily migrated on new feature.
NVARCHARis supported by all SQL components so is the JSON too.
SELECT column, expression, column as alias FROM table1, table2, table3 FOR JSON [AUTO | PATH]
It is a
SELECTcommand so when we fire the above query, SQL will format each row/cell value and return as JSON object.
SQL has also provided in-built functions for JSON.
Dynamic Data Masking
After masking, SQL User with limited rights will not view original text, he can view only Masked Text, SQL has pre-defined masking functions you just need to apply it on different columns, see below:
|Sr No||Functions||Applied on||Plain text (Input)||Masking text(output)|
To apply this on specific columns, you just need to
ALTERcolumn with '
MASKED WITH' function name, see below syntax:
//here I used function as Default(), you can change it to any of the above types ALTER TABLE tablename ALTER COLUMN columnname MASKED WITH (FUNCTION=‚default()‘)
Row Level Security
This is again one of the security features of SQL 2016. It allows you to secure your data row wise, in short you can define a row, that will be viewed by a particular SQL user only. So depending upon the SQL user access permission, we can restrict row level data, e.g., we can ensure if employees can view only their department data though department table is the same.
To implement Row level security, you need to define Security policy with a predicate and function.
We need to create a policy for security, here is simple syntax:
CREATE SECURITY POLICY fn_security ADD [FILTER | BLOCK] PREDICATE FunctionName ON TableName
In the above syntax,
BLOCKare the predicates that will either
FILTERrows and display only those that are available for read or
BLOCKrows for write operation.
Function: Function is a simple user defined function, but here are some restrictions for user defined function that are used in Row Level Security syntax:
- Database modification operations are not allowed
OUTPUT INTOclause is not allowed in function
- Multiple result set should not be returned from function
To configure it, you need an Azure account and database instance that you need to stretch. The following snap will clear your idea.
It is always a good practice to have a Multiple Temp data files, if you are working on a big crucial data, up till now (SQL 2014), you need to manually add temp db files to your database but SQL 2016 provides you temp DB configuration settings, in which you can configure Number of TempDB files at the time of SQL installation. Default number of files are 8 with default size of 64 MB will be given.
So you no longer need to configure/create it manually.
To enable it, just right click on database (obviously, you need SQL 2016 SSMS), go to properties. You will see 'Query store' at the left corner, select it and click on Enable '
true' or you can do it using Query as follows:
ALTER DATABASE [Database1] SET QUERY_STORE = ON
How It WorksBasically, the system keeps pair of a table for history and adds two additional columns in it named '
SysStartTime' and '
SysEndTime' for start time and end time for row respectively. Live table contains current record of row, whereas history table contains previous record of row. We can fetch data from History table, with the following query:
SELECT * FROM table1 FOR SYSTEM_TIME BETWEEN date1 AND date2 WHERE condition;
R Introductionstatistical data in SQL? Want to use R to analyze it? You export data each time from SQL to R? Then your headache will now be covered in SQL 2016, because it is now with R. You can run R script on SQL. You need to install this feature at the time of SQL setup.
We cannot cover all features in details, maybe I will be planning soon to cover them one by one.
Till then, you can enjoy this article.
Suggestion and queries are always welcome.
**This article is already published at CodeProject