Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
I tested the Dynamic Data Masking feature in my SQL Server 2016 test environment, and I want to share the result with you. I used the PhoneNumber field in the AdventureWorks2012.Person.PersonPhone table for the test. Using the Dynamic Data Masking feature, I mask 3 characters in the middle of the values in the PhoneNumber field created with the NVARCHAR (25) data type with the following commands.
Before masking:
The commands I use for masking:
1 2 3 4 | USE [AdventureWorks2012] GO ALTER TABLE Person.PersonPhone ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'partial(3,"-XXX-",4)'); |
If I wanted to, I wouldn’t show any data about phone numbers and / or I would use as many “X” numbers as I’d like, and I couldn’t give any idea about how many characters would be.
You can also remove this feature in a masked area by using the following command:
1 | ALTER TABLE Person.PersonPhone ALTER COLUMN PhoneNumber DROP MASKED; |
To use the Dynamic Data Masking feature, the Compatibility Level setting of the database (SQL Server 2016) is required. For example, for a database with a Compatibility Level setting of 90 (SQL Server 2005), this feature is not available.
Although I don’t expect a big negativity in terms of performance, I don’t know what impact it will have on performance; But its use is quite practical, the application side does not require any modification. The only change on the management side is to define the relevant field as MASKED with the command I mentioned above and to grant UNMASK authorization to the required users.
