I've been doing some work with RLS lately (Row-Level Security). I think this is one of the most useful features added in SQL Server 2016, and I wish it had been available earlier in my career. I've had the need to secure multi-tenant databases, which is a chore. Updates and new functionality constantly brought stress on developers and DBAs that a small mistake would expose one client's data to another.

RLS isn't perfect, and while it simplifies the way in which you can apply security to your tables, it does require strong T-SQL understanding and careful thought. While we often can link a user to a customer, we may decide that we also have administrators or super users that need to access all, or many pieces of data. What might seem like a small change, just adding a catch-all clause, might cause problems for performance. This article at MSDN shows the impact, which could be quite large for million (or billion) row tables.

I ran across a developer that was testing access and found performance to be much worse. They were querying AD with OR clauses to allow different groups access, and this ended up causing issues. There are a few ways that this could be solved, but everyone should understand that adding in functions to your code requires more processing of data. This processing can dramatically impact your system, just like any other code, if you haven't written efficient operations.

That's ultimately the litmus test for most code. Is you code written to work efficiently in your environment and with ever growing data sizes? There are certainly places where a trade off might make sense. If you have a system that will never pass many rows through the FORMAT() function, perhaps that's acceptable. If you can't be sure of the data size and it could be large, then you shouldn't ever be using FORMAT().

There are some great features in SQL Server, many of which can help you write better applications. You just need to use them appropriately and in the places where they will shine. RLS is one of these, but there can be a performance impact if you aren't careful. Ensure that you learn to write better code, picking the patterns that work well. No matter what, also ensure that before you deploy your change, you run a full scale performance test to be sure your clients won't be spending most of their time staring at a screen while some query runs in the background.

Join the debate, and respond to today's editorial on the forums

Automatic page repair does not repair pages that are file header pages, database boot pages, or allocation pages.

Ref: Automatic Page Repair - click here

» Discuss this question and answer on the forums