Following on from previous posts where we looked at Dynamic Data Masking, how to use it, and the challenges we may encounter, there’s one last area I wanted to touch on. With the arrival of SQL Server 2022 the masking functionality has been extended so we have an additional masking function and more granular permissions […]
Tag: Querying
Categories
Challenges with Dynamic Data Masking
Recently we’ve been looking at Dynamic Data Masking and what it can achieve for us. With that said however, there are some drawbacks to how it works which its worth being aware of before you consider implementing it. Even in the official documentation it states that the feature “limits” exposure, can be used to “simplify” design, and […]
Categories
Dynamic Data Masking Patterns
When we initially looked at Dynamic Data Masking we used the default function to mask our data so it showed xxxx. With the masking however there are a few functions available to allow us to mask data in particular ways which may be of use depending on our use case. We’ll create a record to apply these masks against so […]
This month’s invitation comes from Mala who asks us about the T-SQL coding rules relevant for where we work. There’s certainly a lot of scope in this question and Mala links to a great set of posts from Robert Sheldon which cover quite a number of these points. Whilst larger businesses may well have matured standards in […]
Categories
Introduction to Dynamic Data Masking
Given the abundance of data we deal with day to day its increasingly likely that we’ll find ourselves storing items of a more sensitive nature. We may only deal with details sensitive about our business such as suppliers or profitability, but when that increases – for example customer details – then we want to ensure […]
Categories
Testing Permissions with EXECUTE AS
When you’re developing a new database solution you’ll inevitably want to apply some security to that data. If you have complex security requirements with users being members of multiple groups or implementing multiple roles within the database it can become tricky to ensure that everything is working exactly as you like. It’s certainly a hassle […]
Categories
Querying Temporal Tables
During our initial introduction we briefly saw how to query temporal tables but there were a couple of specific bits which I wanted to cover around querying these tables. The first of these concerns the point in time we query against and the second is how we go about using that point in time to […]
This month’s invitation from Camila Henrique asks us for advice we’d give to our younger selves in our T-SQL journey. Coming from the SQL Server perspective there’s a huge variety of areas within the stack where you might want to provide some advice but I’m going to focus on the scripting and querying as that’s […]
Categories
Dynamic Date Ranges with Window Functions
After recently covering window functions I thought it would be a good opportunity to share one of my favourite use cases for them. The functions for ranking and aggregations have their own clear use cases but here I’d like to discuss a particular use for the LAG and LEAD functions. The scenario There are instances […]
Categories
Analytical Window Functions
In the previous posts we covered the ranking and aggregate window functions and this time we’ll be finishing the series covering the Analytic functions, seeing what an alternative to Window Functions might look like, and then wrapping up what we’ve covered in this series of posts. As with our previous examples we’ll set up our […]
Andy Brownsword 