This week I gave two sessions at SQLintersection:
| Session | Slide Deck | Demos |
|---|---|---|
| New T-SQL Features in SQL Server 2016 & Azure SQL DB | Download | Download |
| New Security Features in SQL Server 2016 & Azure SQL DB | Download | Download |
Some interesting questions came up, and I wanted to address a couple of them here.
Can you perform operations on masked columns?
No. In the example, we had multiple columns that had masks, including salary and email:
CREATE TABLE dbo.SampleTable
(
salary int MASKED WITH (FUNCTION = 'default()'),
email varchar(320) MASKED WITH (FUNCTION = 'email()')
);
CREATE USER peon WITHOUT LOGIN;
GRANT SELECT ON dbo.SampleTable TO peon;
So, if we insert a few rows, and then perform a select that tries to manipulate the output columns:
INSERT dbo.SampleTable(salary,email)
VALUES(10,'a@bar.com'),(20,'b@bar.com'),(30,'c@bar.com');
EXEC('SELECT salary, salary+1, email, email + ''x''
FROM dbo.SampleTable;') AS USER = N'peon';
We can see that the salary column stays as 0, and the email column reverts to default masking:
salary (No column name) email (No column name)
------ ---------------- ------------- ----------------
0 0 aXXX@XXXX.com xxxx
0 0 bXXX@XXXX.com xxxx
0 0 cXXX@XXXX.com xxxx
How do we control the history table for temporal tables?
One attendee asked if there were any built-in mechanisms to manage retention of data in the history table – after all, if you have a heavy update/delete workload, that history table will get pretty big, pretty fast.
The answer is NO. There is nothing built in to the temporal table syntax to account for configuration of retention; you’re on your own for this. Microsoft has published some documentation with some suggestions:
Their suggestions are:
- Stretch Database
- Table Partitioning*
- Custom Cleanup Script
* This also answers another question that came up: “Can you partition the history table?”
What is the performance impact of the new security features?
Dynamic Data Masking
The performance impact of Dynamic Data Masking should be negligible, according to this blog post. I have not tested this to confirm, other than the casual observation that I didn’t notice any difference; but if you are returning a lot of masked rows or columns, you should test your scenario. If you do find degradation, I’d love to hear about it!
Row-Level Security
I did not test the impact of Row-Level Security, either; in theory, it would totally depend on what logic is in your predicate function. The function itself should be inlined with the query just like any other inline TVF, so don’t think about it like you would a scalar function or multi-statement table-valued function. But if your logic relies on checking other tables to determine access, then that data access can impact the overall query. Some other tips in this post and this post.
Always Encrypted
I did test the performance of Always Encrypted, and found that the main performance impact – which was significant – was on insert speed. You can see my testing methodology and results in the following two posts:
Now, to be fair, that was several builds ago (CTP 2.3), and on a very specific hardware configuration. There may have been some internal performance enhancements in newer builds that I haven’t tested yet, and the performance may be better or worse on different hardware than my VM. There are some additional performance tips in this topic in Books Online.
Generally…
Like other situations, I am more than happy to perform generic testing of features, but to trust the results, you really should test the performance on your own hardware and with your own data and access patterns.
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.
Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.
