Tag: T-SQL

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Dynamic Data Masking is a feature that provides some pseudo-security features. This lets you return a portion of data while hiding other portions for unauthorized users. The classic example is preventing someone from seeing PII data if they are a customer service rep or other non-privileged user.

Note: THIS IS NOT A SECURITY function, though it is somewhat marketed and talked about it this way. I say pseudo-security, but be careful here. I have a larger article on why.

A Scenario

I have a simple table here. I’ll give you some DDL and DML.

create table DDMEmailTest
( MyID int not null identity(1,1) constraint DDMEmailTestPK primary Key
, MyName varchar(100)
, Email varchar(100)
, Salary int)
go
insert DDMEmailTest select 'Steve Jones', 'steve.jones@sqlservercentral.com', 200
insert DDMEmailTest select 'Bob Jones', 'bob.jones@acme.com', 300

Now, if I query this as a normal user, I see something like the image below. Note I can read the email address.

Limiting Access

I can prevent this from occurring by adding Dynamic Data Masking to the column. This is a column level feature, which doesn’t need activating. It’s in SQL Server 2016+ databases. You add masking with an  ALTER COLUMN like shown below. The email() function is built into SQL Server.

alter table DDMEmailTest ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

When I now query the data, I see this:

Users that are not admins, or have been granted the UNMASK permission will get masked data. This mask specifically is the first character and then the XXX@XXXX.com value.

Use it if this fits your scenario.

SQLNewBlogger

I was working with DDM to show something to another person and decided to throw this post together. I’d set up the scenario, so I just had to write. This was about 15 minutes of my day.

You could do the same thing, but explain how you might use this in your organization.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a post on using STRING_SPLIT() with cross apply recently, and as I was reading, I realized that I hadn’t adopted this very often in code. Mostly it’s not something I need to deal with, but recently my wife did give me some data and asked to reformat it, and I used STRING_SPLIT(). I thought it was a nice easy problem, so I decided to write about it.

Conflated Data

The dataset that she gave me was formatted something like this:

What she wanted was a report that provided data more like this:

This was more complex in the past, and hard in T-SQL as string manipulation isn’t great. However, STRING_SPLIT makes this easier. Not great, but easier.

STRING_SPLIT

While STRING_SPLIT is a function, it is a table-valued function, meaning that it is used as the source for a data set. Rather than being in the SELECT column list, we would use this in the FROM clause as another table. Or anyplace a table can be used.

The basics of this would be:

SELECT ss.value
  FROM dbo.ClassSchedule AS cs
   CROSS APPLY STRING_SPLIT(students, ',') AS ss

Using this code, I would get a list of students.

The STRING_SPLIT returns this single column, value, which is the string passed in split by the separator. I could see this more simply with this code:

SELECT * 
  FROM STRING_SPLIT('alpha,beta,delta,gamma',',') AS ss

This returns these items:

A few more examples:

The important thing to note here is that this is a simple substring based on the separator, with no other processing. Spaces or any other character outside the separator is left in the result.

There also is no ordering of the results. While this appears to be in the same order in these simple examples, this is not guaranteed, meaning the ordering of the substrings might not match the original order.

This is a good way to easily split up data that you have stuffed into a large character field, but it has limitations, so use it carefully.

SQLNewBlogger

This post took me about 15 minutes to write after spending 5 minutes setting up some test data. I was using string_split to clean up some data and decided to make a few notes, then built a new table with some strings in it to help me explain how it works.

A good example of a post you could write, perhaps noting when this was released or comparing this to a way of splitting these strings with a more complex T-SQL query.