Voice of the DBA

Tag: SQLNewBlogger

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
Reading Data from the Command Line in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In every language I’ve coded in, there is a way to read input from the command line. From INPUT in BASIC, readln in PASCAL, scanf in C, read in LISP, input() in Python, and more, every language can do this.

I knew PowerShell could, and I wanted to find out how to do it. I’m building a CLI tool for SQL Saturday, and I am looking to get user input.

A quick search led me to Read-Host. I probably should have guessed this, but it does what I need. A quick example of where I started. This code gets an event number from a user and then prints if back out, along with a string created from the number:
```
$EventNumber = Read-Host -Prompt "Event Number:"
$DataFilename = "SQLSat$($EventNumber).yml"
# write the data
write-host("Event: $EventNumber")
write-host("File: $DataFilename")
```
I can run this, as shown here, and put in a number. I typed in the first 1022 below:

I’ll use this to get information from the user and then produce a YAML file that will contain what’s needed to publish, or update, an event.

SQLNewBlogger

This post took me about 10 minutes to assemble. I was already working through this process and stopped to jot down an idea, save some code, and make a screenshot. That was about 2 minutes out of my day, and then I went on with coding. Later, I wrote this.

You could do the same thing. Take a minute out of your daily work, sketch a quick post, and then finish it later.
November 22, 2021