Voice of the DBA

Tag: SQLNewBlogger

Changing Case in SSMS–#SQLNewBlogger

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

I never knew I could change case for objects in SSMS easily. This actually was something that another individual pointed out to me, but once I tried it, I liked it and know I’ll use it at times.

CTRL+Shift+L will lowercase whatever text is selected in SSMS. A quick gif to show this below, where I select a portion of the code and then CTRL+Shift+U to upper case it, which is what I’d want. I then CTRL+Shift+L to put it back.

Normally I use SQL Prompt to do these types of things, but that reformats the entire section of code. I don’t always want to do that. I could certainly CTRL+K, Y, which would upper case this according to my current format, but I can’t change to lower case easily. Knowing these shortcuts makes it easy for me to match case if needed.

SQLNewBlogger

When I ran across this and realized it was new, I just wrote this post. It took about 5 minutes and I spent more time deciding if I should record the screen than it took to just do it.

You could easily write this type of post as you learn small things. Tag them with SQLNewBlogger as well to encourage others.

December 13, 2021
2021 Advent of Code–#SQLNewBlogger

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

I’ll do a post on how to easily get started here as a new blogger, but following the Advent of Code, even some random problems, is a good way to show off some T-SQL skills.

This won’t be a goal for me, but I did start working on the 2021 Advent of Code, taking a few minutes across some days to break from other work and solve a programming problem.

My aim this time is not to get stuck on a problem. If I can’t solve it, or don’t see a way, I leave it and move on. This post has a few thoughts on the first few days.

Day 1

The first problem dealt with loading a set of data and then counting how many times the number increases from the previous number.

Since numbers in a SQL table don’t have a guaranteed order, this was a bit of a hack from me. I created a table and added a clustered index, and then bulk inserted the data. I then moved this in the same order (I hope) to a table with an identity column. From here, simple LEAD to find the differences between consecutive rows and counting these.

The second part changed the calculation slightly to use groups of three rows. I copied my LEAD formula to include 3 rows instead of just 1 in each side of the calculation.

Day 2

We’re in a submarine, moving forward or up/down. The input was something I needed to evaluate in order again, so I repeated a similar load. Then I used SUBSTRING and CASE to decide what type of instruction was needed and sum the results.

Part 2 was tricky. I bailed initially, as I couldn’t quite get the math down in my head. I eventually set up a small test data set using the values on the site and then used that to calculate things. I had a series of CTEs that I used to extract the values, then get changes, then perform the math.

Letting part 2 sit for a day in my head helped me focus better.

Day 3

Day 3 was fairly easy binary counting. The test data doesn’t depend on order, so I just loaded it up. Then I need to extract the values into the bits, so SUBSTRING each of these out in a CTE. Not dynamic, but it was easy to extract all 12 bits, then count up the number of 1s and 0s, deciding which was more prevalent.

From there, a simple calc to assemble back the counts into a binary number and convert to decimal.

Part 2 is really about counting the 1s and 0s in each position, then creating a final binary number from this and converting back to decimal. I had to read carefully here, as you need to reduce your input set each time. I ended up looping here, as I couldn’t find an easy way to do this otherwise. I could have added some flag to ignore rows, but ended up with a temp table and deletes to get this done.

So far, easy, harder, then easier.

December 10, 2021
Getting Started with the Advent of Code as a #SQLNewBlogger

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

If you want to get started blogging, or revitalize your work, here is a good way to practice some T-SQL, have some fun, and build a blog.

This is a simple set of steps, but all of these will help you in the future as you expand your skills and blogging.

Online Accounts

First, create a blog. Go to WordPress and create an account for free.

Get a GitHub account. No reason to not have one these days, and it’s free. I won’t walk you through this part, but sign up.

Next, create a repository. In the repositories section, click “New” in the upper right. You can see my AdventofCode repository is already created. Make your own in your account.

Next, go to the Advent of Code site and log in with Github. It’s one of the auth settings. Don’t worry if you aren’t doing this in time with others. I’ve worked on this throughout the year. You can also go through previous years if you choose.

The Workstation Setup

First, install Git. This is easy and free as well. Once you have, open a command prompt. Pick a place where you want to store code and create a “git” folder. Change to this folder.

In your Advent of Code repository, click the “Code” button in the upper right. This will open a drop down. Copy the https URL shown in there. You can see mine highlighted below.

In your command prompt, type “git clone “ (space here), then paste in your repo path and hit Enter. It should look something like this:

This has downloaded the repo to this folder and linked it with my GitHub repo. This will be empty for you, but here’s what I’d suggest. Create a “2021” folder and then start putting code in there. You can see I have a few folders listed below.

Inside the 2021 folder, I tend to separate out days. For me, I do this because I save the input files as input.txt. You can organize your code any way you want, but write about how and why. Here’s my folder.

I have solved problems multiple ways in the past. Here’s last year’s Day 1, solved in T-SQL, Python, and PowerShell.

Now write code and solve the puzzles.

Saving Code

You should learn git, but here is the easy way to do things. As you write code, run a “git status” in your repo (any folder). You’ll either see everything is clean and up to date, or something like this:

When you want to save something, type “git add –all”. This adds all files to the staging area. There isn’t a return here, but if you run status, you’ll see things are staged.

Now commit a version with this:

git commit –m “xxxx”

Replace XXX with some comment. For me, I often will say which days I’ve solved. In this case, I haven’t solved day 4 yet, but I just added a note.

Now type “git push” to send your code to GitHub. You’ll get something like this.

That’s it.

Now you’ve learned how to move code around and source control it, you have puzzles to play with, and you can blog about how to approach problems, and how you are saving code. In fact, rewrite this post as you set up your own environment.

SQLNewBlogger

Here’s a great way to showcase knowledge and improve skills. This post took me about 15 minutes to setup and outside of creating accounts or installing git, I bet you could do it in the same time.

Give it a try. Start your blog today.

December 8, 2021
Hiding Email with a Dynamic Data Masking Function–#SQLNewBlogger
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.
November 29, 2021