NEW T-SQL FEATURES IN SQL SERVER 2016 – Temporal Table : Hidden Column

08 Thursday Mar 2018

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

≈ 2 Comments

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016 – Temporal Table : Hidden Column

In this post we shall check out the hidden column feature for Temporal Table columns

Syntax with [ HIDDEN ] attribute

--

--Simple CREATE TABLE Syntax (common if not using options)  
CREATE TABLE   
    [ database_name . [ schema_name ] . | schema_name . ] table_name   
    ( { <column_definition> } [ ,...n ] )  
	..[ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]  
[ ; ]  

--

Now let’s take an example. Create a temporal Table called temporalTable1.

--

CREATE TABLE temporalTable1
(
    Id INT PRIMARY KEY
  , Val Varchar(100)
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END   
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HistorytesttemporalTable1));  
GO  

--

Now If you execute SELECT * from temporalTable1 you will get data from all the columns.


SELECT * FROM temporalTable1


--OUTPUT

Id          Val        ValidFrom       ValidTo      
----------- ---------- ------------ -- ------------ 
                                                    
(0 rows affected)                                   
                                                    
--

Note that columns like ValidFrom and ValidTo are visible here. Now we can make them hidden by using below method.

--

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidFrom] ADD HIDDEN
GO

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidTo] ADD HIDDEN
GO                              
                                                    
--OUTPUT

	Commands completed successfully.

Now again execute SELECT * from temporalTable1 and see if you get data from all the columns.


SELECT * FROM temporalTable1


--OUTPUT

Id          Val
----------- -----------

(0 rows affected)
                                
                                                    
--

See here you will get only first two columns only. Remaining date columns will not be SELECTED.

If you want unhide the columns then use below command.

--

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidFrom] DROP HIDDEN
GO

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidTo] DROP HIDDEN
GO
                            
                                                    
--

Notes

1. For hidding a column GENERATED ALWAYS AS ROW START/END is compulsory.
2. System-versioned table cannot have more than one ‘GENERATED ALWAYS AS ROW END’ column
3. System-versioned table cannot have more than one ‘GENERATED ALWAYS AS ROW START’ column

Refer Microsoft Link below for more details-

1	https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

Enjoy !!!

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

SQL Puzzle | Convert JSON Integer array to Table rows | SQL Interview Question

13 Tuesday Feb 2018

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles

≈ Leave a comment

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, Convert JSON Integer array to table rows, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, Get Next Value Puzzle, Huge blank areas in database field, Improve SQL Skills, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, 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 rowset view, JSON text to result set, JSON to rows in Sql, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Objective Puzzle, OPENJSON, OPENJSON Sql, OPENJSON Sql function, OPENJSON Sql Server, OPENJSON with Schema declaration, OPENJSON with Schema definition, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Remove Huge Multiple Spaces from a DB Fields, Remove Multiple Spaces from a DB Fields, Separate Int and char from a string column, Single Quote update SQL, SQL, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, Sql Server 2016, Sql Server 2016JSON Functions, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The Status Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries

SQL Puzzle | Convert JSON Integer array to table rows | SQL Interview Question

In this puzzle you have to parse the JSON integer array to get data in a single column. Can you do that in a single select?. Please check the sample input and the expected output.

Notes –
1. The limitation is that we can only use a single SELECT for the puzzle.

Sample Input

Employees Table

jsonIntegerArray
[1,2,3,4,5,6,7,8,9]

Expected Output

Value
1
2
3
4
5
6
7
8
9

Use below script to create table and insert sample data into it.

--

DECLARE @jsonIntegerArray AS VARCHAR(MAX);
SET @jsonIntegerArray = '[1,2,3,4,5,6,7,8,9]';
SELECT @jsonIntegerArray

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | SOLUTION USING OPENJSON

--



DECLARE @jsonIntegerArray AS VARCHAR(MAX);
SET @jsonIntegerArray = '[1,2,3,4,5,6,7,8,9]';
SELECT Value from OPENJSON(@jsonIntegerArray)


--

Output-1

--                            

Value
-------------------------
1
2
3
4
5
6
7
8
9

(9 rows affected)


--

OPENJSON	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/
ISJSON	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xiii-isjson/
JSON_VALUE	https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xv-json_value/
JSON_MODIFY	https://msbiskills.com/2018/01/23/new-t-sql-features-in-sql-server-2016-xvii-json_modify/

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

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

Improving my SQL BI Skills

Tag Archives: Sql Server 2016

NEW T-SQL FEATURES IN SQL SERVER 2016 – Temporal Table : Hidden Column

SQL Puzzle | Convert JSON Integer array to Table rows | SQL Interview Question

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

Share this

Share this

Share this

Share this

Share this