NEW FEATURES IN SQL SERVER 2016 | Complete List

26 Friday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

NEW FEATURES IN SQL SERVER 2016 | Complete List

In this post we shall check out all the new features Microsoft introduced in SQL Server 2016

#	New Feature	URL

1	DROP IF EXISTS	https://msbiskills.com/2018/01/06/new-t-sql-features-in-sql-server-2016-i/
2	STRING_SPLIT()	https://msbiskills.com/2018/01/07/new-t-sql-features-in-sql-server-2016-ii-string_split/
3	CREATE OR ALTER	https://msbiskills.com/2018/01/08/new-t-sql-features-in-sql-server-2016-iii-create-or-alter/
4	DBCC CLONEDATABASE(Service Pack 1)	https://msbiskills.com/2018/01/08/new-t-sql-features-in-sql-server-2016-iv-dbcc-clonedatabase/
5	DATEDIFF_BIG()	https://msbiskills.com/2018/01/09/new-t-sql-features-in-sql-server-2016-v-datediff_big/
6	STRING_ESCAPE()	https://msbiskills.com/2018/01/09/new-t-sql-features-in-sql-server-2016-vi-string_escape/
7	USE HINT query hint argument	https://msbiskills.com/2018/01/10/new-t-sql-features-in-sql-server-2016-vii-use-hint-query-hint-argument/
8	TRUNCATE TABLE WITH ( PARTITIONS ( … ) )	https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-viii-truncate-table-with-partitions/
9	Session_Context()	https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-ix-session_context/
10	Row Level Security	coming soon….
11	Always Encrypted	coming soon….
12	Stretch Database	coming soon….
13	Temporal table	https://msbiskills.com/2018/01/21/new-t-sql-features-in-sql-server-2016-xi-temporal-tables/
13.1	Temporal table:HIDDEN COLUMN	https://msbiskills.com/2018/03/08/new-t-sql-features-in-sql-server-2016-temporal-table-hidden-column/
14	Built in JSON Support	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xiv-json-support-in-sql-server-2016/
15	Dynamic Data Masking	https://msbiskills.com/2018/01/23/new-t-sql-features-in-sql-server-2016-xviii-dynamic-data-masking/
16	QueryStore	coming soon….
17	Polybase	https://msbiskills.com/2018/01/25/new-t-sql-features-in-sql-server-2016-xix-polybase/
18	R-Integration	coming soon….
19	FormatMessage()	https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-x-formatmessage/
20	OPENJSON Function	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/
21	ISJSON Function	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xiii-isjson/
22	JSON_VALUE Function	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xv-json_value/
23	JSON_MODIFY Function	https://msbiskills.com/2018/01/23/new-t-sql-features-in-sql-server-2016-xvii-json_modify/

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016 – XIX | PolyBase

25 Thursday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016 – XIX | PolyBase

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | PolyBase

New feature – XIX | PolyBase

PolyBase is a technology that allows us to access data outside of the database via the t-sql language. We can access data from Azure Blob Storage, Hadoop and Parallel Data Warehouse.

From Microsoft – PolyBase allows you to run queries on external data in Hadoop or to import/export data from Azure Blob Storage. Queries are optimized to push computation to Hadoop. In Azure SQL Data Warehouse, you can import/export data from Azure Blob Storage and Azure Data Lake Store.

PolyBase can be used to analyze both relational data and other data that is not structured into tables —notably Hadoop. This is difficult to do unless you have a way to transfer data among the different types of data stores. PolyBase bridges this gap by operating on data that is external to SQL Server.

PolyBase does not require you to install additional software to your Hadoop environment. Querying external data uses the same syntax as querying a database table. This all happens transparently. PolyBase handles all the details behind-the-scenes, and no knowledge about Hadoop is required by the end user to query external tables.

SQL Server 2016 New Features – Polybase

**-From Microsoft

PolyBase can be used to –

1. Query data stored in Hadoop from SQL Server or PDW via T-SQL.
2. Query data stored in Azure Blob Storage via T-SQL.
3. Import data from Hadoop, Azure Blob Storage, or Azure Data Lake into relational DB using T-SQL. With this we do not need additionl ETL.
4. Export data to Hadoop, Azure Blob Storage, or Azure Data Lake Store.
5. For Microsoft’s business intelligence and analysis stack, or use any third party tools that are compatible with SQL Server.

PolyBase Installation

To install PolyBase please follow the instructions from below Microsoft URL.

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-installation

Confirm Installation

To confirm the installation please execute the query given below and if the query returns 1 then it is installed and if it returns 0 then it is not installed.

--

SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled; 

--

Configure PolyBase

To configure PolyBase please refer the URL from Microsoft.

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/get-started-with-polybase

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/polybase/get-started-with-polybase
2. https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking

23 Tuesday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | Dynamic Data Masking

New feature – XVIII | Dynamic Data Masking

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. DDM can be configured on the database to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

**-From Microsoft

More Details-

1. A central data masking policy acts directly on sensitive fields in the database.

2. Designate privileged users or roles that do have access to the sensitive data.

3. DDM features full masking and partial masking functions, as well as a random mask for numeric data.

4. Simple Transact-SQL commands define and manage masks.

Types of masks available are
1. Default

2. Email

3. Random

4. Custom String

Examples

Prepare data for examples of Dynamic Data Masking

--

CREATE TABLE testDataMASKING
(
	  Id INT
	 ,[Name] VARCHAR(100)
	 ,DOJ DATETIME
	 ,EmailId VARCHAR(100)
	 ,Cell VARCHAR(20)
	 ,Salary DECIMAL(38,2)
)
GO

INSERT INTO testDataMASKING VALUES
(1,'Pawan',GETDATE() - 100 , 'Pawan@MSBISKILLS.COM','917-470-9890', 100000),
(2,'Ramesh',GETDATE() - 20 , 'Ramesh@MSBISKILLS.COM','917-471-9890',1900000),
(3,'Avtaar',GETDATE() - 19, 'Avtaar@MSBISKILLS.COM','917-470-9810',200000),
(4,'Sharlee',GETDATE() - 200, 'Sharlee@MSBISKILLS.COM','917-470-3890',300000),
(5,'Vaibhav',GETDATE() - 10, 'Vaibhav@MSBISKILLS.COM','917-470-9000',4000000),
(6,'Ishu',GETDATE() - 18, 'Ishu@MSBISKILLS.COM','917-470-9981',900000),
(7,'Chandar',GETDATE() - 19, 'Chandar@MSBISKILLS.COM','917-470-1234',190000),
(8,'Kishan',GETDATE() - 45, 'Kishan@MSBISKILLS.COM','917-470-7634',180000)
GO

--

/*Create a Read only user*/

--

CREATE USER OnlyRead WITHOUT LOGIN
GRANT SELECT ON testDataMASKING TO OnlyRead
GO

--

Current data that we need to mask

--

SELECT * FROM testDataMASKING

Id          Name                    DOJ                     EmailId                     Cell                 Salary        
----------- --------------- ------- ----------------------- --------------------------  -------------------- ------------- 
1           Pawan                   2017-10-15 14:30:29.903 Pawan@MSBISKILLS.COM        917-470-9890         100000.00     
2           Ramesh                  2018-01-03 14:30:29.903 Ramesh@MSBISKILLS.COM       917-471-9890         1900000.00    
3           Avtaar                  2018-01-04 14:30:29.903 Avtaar@MSBISKILLS.COM       917-470-9810         200000.00     
4           Sharlee                 2017-07-07 14:30:29.903 Sharlee@MSBISKILLS.COM      917-470-3890         300000.00     
5           Vaibhav                 2018-01-13 14:30:29.903 Vaibhav@MSBISKILLS.COM      917-470-9000         4000000.00    
6           Ishu                    2018-01-05 14:30:29.903 Ishu@MSBISKILLS.COM         917-470-9981         900000.00     
7           Chandar                 2018-01-04 14:30:29.903 Chandar@MSBISKILLS.COM      917-470-1234         190000.00     
8           Kishan                  2017-12-09 14:30:29.903 Kishan@MSBISKILLS.COM       917-470-7634         180000.00     
                                                                                                                           
(8 rows affected)   

--

Example of Default Function Masking for Name and DOJ column

--
     
ALTER TABLE testDataMASKING
	   ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION='DEFAULT()')
	 
ALTER TABLE testDataMASKING
	   ALTER COLUMN DOJ ADD MASKED WITH (FUNCTION='DEFAULT()')

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

OUTPUT

Id          Name           DOJ                     EmailId                     Cell                 Salary            
----------- -------------- ----------------------- --------------------------- -------------------- ----------------- 
1           xxxx           1900-01-01 00:00:00.000 Pawan@MSBISKILLS.COM        917-470-9890         100000.00         
2           xxxx           1900-01-01 00:00:00.000 Ramesh@MSBISKILLS.COM       917-471-9890         1900000.00        
3           xxxx           1900-01-01 00:00:00.000 Avtaar@MSBISKILLS.COM       917-470-9810         200000.00         
4           xxxx           1900-01-01 00:00:00.000 Sharlee@MSBISKILLS.COM      917-470-3890         300000.00         
5           xxxx           1900-01-01 00:00:00.000 Vaibhav@MSBISKILLS.COM      917-470-9000         4000000.00        
6           xxxx           1900-01-01 00:00:00.000 Ishu@MSBISKILLS.COM         917-470-9981         900000.00         
7           xxxx           1900-01-01 00:00:00.000 Chandar@MSBISKILLS.COM      917-470-1234         190000.00         
8           xxxx           1900-01-01 00:00:00.000 Kishan@MSBISKILLS.COM       917-470-7634         180000.00         
                                                                                                                      
(8 rows affected)    

--

Example of Function Masking for Email

--

ALTER Table testDataMASKING
ALTER COLUMN EmailId ADD MASKED WITH (FUNCTION='Email()')

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

OUTPUT

Id          Name            DOJ                     EmailId                  Cell                 Salary         
----------- --------- ----- ----------------------- -------------------- --- -------------------- -------------- 
1           xxxx            1900-01-01 00:00:00.000 PXXX@XXXX.com            917-470-9890         100000.00      
2           xxxx            1900-01-01 00:00:00.000 RXXX@XXXX.com            917-471-9890         1900000.00     
3           xxxx            1900-01-01 00:00:00.000 AXXX@XXXX.com            917-470-9810         200000.00      
4           xxxx            1900-01-01 00:00:00.000 SXXX@XXXX.com            917-470-3890         300000.00      
5           xxxx            1900-01-01 00:00:00.000 VXXX@XXXX.com            917-470-9000         4000000.00     
6           xxxx            1900-01-01 00:00:00.000 IXXX@XXXX.com            917-470-9981         900000.00      
7           xxxx            1900-01-01 00:00:00.000 CXXX@XXXX.com            917-470-1234         190000.00      
8           xxxx            1900-01-01 00:00:00.000 KXXX@XXXX.com            917-470-7634         180000.00      
                                                                                                                 
(8 rows affected)      

--

Example of Partial Masking for Cell column

--

ALTER Table testDataMASKING
ALTER COLUMN Cell ADD MASKED WITH (FUNCTION = 'partial(1,"p",1)')  

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

OUTPUT

Id          Name         DOJ                     EmailId            Cell       Salary         
----------- ---------- - ----------------------- -----------------  ------- -- -------------- 
1           xxxx         1900-01-01 00:00:00.000 PXXX@XXXX.com      9p0        100000.00      
2           xxxx         1900-01-01 00:00:00.000 RXXX@XXXX.com      9p0        1900000.00     
3           xxxx         1900-01-01 00:00:00.000 AXXX@XXXX.com      9p0        200000.00      
4           xxxx         1900-01-01 00:00:00.000 SXXX@XXXX.com      9p0        300000.00      
5           xxxx         1900-01-01 00:00:00.000 VXXX@XXXX.com      9p0        4000000.00     
6           xxxx         1900-01-01 00:00:00.000 IXXX@XXXX.com      9p1        900000.00      
7           xxxx         1900-01-01 00:00:00.000 CXXX@XXXX.com      9p4        190000.00      
8           xxxx         1900-01-01 00:00:00.000 KXXX@XXXX.com      9p4        180000.00      
                                                                                              
(8 rows affected)       

--

Example of Random Masking on column Salary

--

ALTER Table testDataMASKING
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION='Random(0,7)')	

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;     

OUTPUT       

Id          Name      DOJ                     EmailId              Cell      Salary     
----------- --------- ----------------------- -------------------- ------- - ---------- 
1           xxxx      1900-01-01 00:00:00.000 PXXX@XXXX.com        9p0       3.69       
2           xxxx      1900-01-01 00:00:00.000 RXXX@XXXX.com        9p0       1.24       
3           xxxx      1900-01-01 00:00:00.000 AXXX@XXXX.com        9p0       3.35       
4           xxxx      1900-01-01 00:00:00.000 SXXX@XXXX.com        9p0       2.90       
5           xxxx      1900-01-01 00:00:00.000 VXXX@XXXX.com        9p0       1.98       
6           xxxx      1900-01-01 00:00:00.000 IXXX@XXXX.com        9p1       0.05       
7           xxxx      1900-01-01 00:00:00.000 CXXX@XXXX.com        9p4       0.12       
8           xxxx      1900-01-01 00:00:00.000 KXXX@XXXX.com        9p4       5.51       
                                                                                        
(8 rows affected)   

--

Drop All Masks and check data for readonly user

--

ALTER TABLE testDataMASKING 
ALTER COLUMN Salary DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN Cell DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN EmailId DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN DOJ DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN [Name] DROP MASKED

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

--

OUTPUT after Masking removed for Onlyread user

--

Id          Name            DOJ                     EmailId                          Cell                 Salary         
----------- ----------- --- ----------------------- ---------------------------- --- -------------------- -------------- 
1           Pawan           2017-10-15 14:30:29.903 Pawan@MSBISKILLS.COM             917-470-9890         100000.00      
2           Ramesh          2018-01-03 14:30:29.903 Ramesh@MSBISKILLS.COM            917-471-9890         1900000.00     
3           Avtaar          2018-01-04 14:30:29.903 Avtaar@MSBISKILLS.COM            917-470-9810         200000.00      
4           Sharlee         2017-07-07 14:30:29.903 Sharlee@MSBISKILLS.COM           917-470-3890         300000.00      
5           Vaibhav         2018-01-13 14:30:29.903 Vaibhav@MSBISKILLS.COM           917-470-9000         4000000.00     
6           Ishu            2018-01-05 14:30:29.903 Ishu@MSBISKILLS.COM              917-470-9981         900000.00      
7           Chandar         2018-01-04 14:30:29.903 Chandar@MSBISKILLS.COM           917-470-1234         190000.00      
8           Kishan          2017-12-09 14:30:29.903 Kishan@MSBISKILLS.COM            917-470-7634         180000.00      
                                                                                                                         
(8 rows affected)                                                                                                        

--

Notes

1. Creating a mask on a column does not prevent updates to that column.
2. Any user can update the data if they have write permissions.
3. If you use SELECT INTO or INSERT INTO to copy data from a masked column then you will get masked data in the target table.
4. Dynamic Data Masking is also applicable on SQL Server Import and Export.
5. Masking is not applicable on following column types – Encrypted columns (Always Encrypted), FILESTREAM, COLUMN_SET or sparse column and computed column.

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016 – XIII | ISJSON()

22 Monday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ 2 Comments

Tags

ISJSON Function, ISJSON function in Sql, ISJSON function in Sql Server, ISJSON function in Sql Server 2016, ISJSON function Sql, JSON, JSON Functions, JSON Functions in Sql, JSON Functions in Sql Server, JSON Functions in Sql Server 2016, JSON in Sql, JSON in Sql Server, JSON in Sql Server 2016, JSON Sql, SQL, Sql 2016, Sql ISJSON, SQL Server, Sql Server 2016, Sql Server 2016ISJSON

NEW T-SQL FEATURES IN SQL SERVER 2016 – XIII | ISJSON()

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | ISJSON()

New feature – XIII | ISJSON()

This function checks whether a string contains valid JSON or NOT.

Notes

1. In SQL Server 2016 JSON will be represented as NVARCHAR type.
2. Example of valid JSON

--
	'{
		 "name":"Pawan"
		,"MiddleName":"Kumar"
		,"Surname":"Khowal"
		,"Class":10
		,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
		,"IsDatabaseDeveloper":true
	}';
--

3. This function returns 1 if the string contains valid JSON; otherwise, returns 0. Returns null if expression is null.
4. ISJSON does not check the uniqueness of keys at the same level.
5. ISJSON does not does not return any errors.

/*Example of Valid JSON*/

--

SELECT ISJSON(
'{
	 "name":"Pawan"
	,"MiddleName":"Kumar"
	,"Surname":"Khowal"
	,"Class":10
	,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
	,"IsDatabaseDeveloper":true
 }')

OUTPUT
 
-----------
1

(1 row affected)


--

/*Example of InValid JSON*/

--

SELECT ISJSON(
'{
	 Kumar
 }')

OUTPUT

-----------
0

(1 row affected)


--

/*Example of NULL JSON*/

--

SELECT ISJSON(NULL)

-----------
NULL

(1 row affected)


--

Usage

/*Example of ISJSON Usage – 1*/

--

DECLARE @param NVARCHAR(MAX)
SET @param = '{
	 "name":"Pawan"
	,"MiddleName":"Kumar"
	,"Surname":"Khowal"
	,"Class":10
	,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
	,"IsDatabaseDeveloper":true
 }'

IF (ISJSON(@param) > 0)  
BEGIN  
     -- Do something 
END

--

/*Example of ISJSON Usage – 2*/

--

SELECT *
FROM table1
WHERE ISJSON(JSONColumn) > 0 

--

Related Post

1. https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Improving my SQL BI Skills

Tag Archives: Sql 2016

NEW FEATURES IN SQL SERVER 2016 | Complete List

NEW T-SQL FEATURES IN SQL SERVER 2016 – XIX | PolyBase

NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking

NEW T-SQL FEATURES IN SQL SERVER 2016 – XIII | ISJSON()

Share this

Share this

Share this

Share this