Voice of the DBA

Author: way0utwest

Increasing the Accuracy of Data We Rely On

When I was a kid, we used physical maps and verbal directions. That, with a good memory and sense of direction helped me navigate across the US on my bike and through many cities in Europe on my bicycle.

Near the turn of the millennium, we got a number of mapping sites, like MapQuest, which were really designed to replace a paper map with a route drawn in. Since then, the capabilities of GPS combined with the advances of mobile phone OS and app improvements, much of the world depends on GPS working correctly.

The GPS systems, of which there are many run by different governments, have been in place for years, and have been upgraded at different times. The US government is looking to upgrade GPS (their system) with GPS III, with stronger signals and a longer lifespan. There are a few other items, better security among them.

There is a lot of GPS data that many of us take for granted. We may not worry about the actual data values, but we may use apps or include integration with services like Google Maps or Apple Maps, and we depend on this data being accurate and available.

There could be similar types of data that our applications and systems need, where availability, accuracy, and more affect the usefulness and value of some application. I think things like master data and data classification fall into these areas. The problem is that often those projects, or the idea of a project, don’t have a large government agency with a big budget working on them. As a result, they rarely move forward.

Steve Jones

December 1, 2020
Social: Using Tech to Raise Money

In 2020 for the first time, I donated to a few political campaigns. I’ve never done that before, though my wife has. I was inspired, and I was glad I helped out, and the candidates I supported went 1-1. That’s fine, as I believed in both of them.

Last week I saw a tweet from Jeff Atwood, fellow UVA alum and founder of Stack Overflow. He was raising money for the Georgia Senate run-off in January.

I’d seen a note from him the week before, but was busy and ignored it. This time, I decided to donate. I did so immediately, and got a text from my wife right away. She watches our money closely and asked if I’d actually donated to the GA campaigns. I had, and got a thumbs up back.

I was stunned when I got a PM from him informing me that I’d won the iPad. I’ve almost never won anything, but I often don’t even bother to enter contests.

Jeff has been running contests to help raise money for the Democratic candidates, and he’s giving away tech gifts to inspire people. This week the prize is a Macbook Air, with the M1 processor, courtesy of Brent Ozar.

If you believe in Jon Ossoff, and want to prevent some gridlock in DC during the next few years consider donating. The chance to win a prize is icing on the cake, but the chance to affect change is more important. I’ve listened to the Democratic candidates, and I think they are good people, looking to improve their state and our country. Character is important, and I think both candidates have that character.

My opinion, I also think that remarks from their opponents lack the character I prefer, putting their party above the well-being of the country and its citizens.

December 1, 2020
Daily Coping 1 Dec 2020

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to set aside regular time to pursue an activity you love.

For me, I don’t know I love a lot of things, but I do enjoy guitar and I’ve worked on it a lot this year. The last month, I’ve let it go a bit, but I’m getting back to it. I try to remember to bring it downstairs to my office, and I’ll take some 5-10 minute breaks and play.

I’ve also started to put together a little time on Sat am to work through a course, and build some new skills.

December 1, 2020
Basic JSON Queries–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I saw Jason Horner do a presentation on JSON at a user group meeting. I’ve lightly looked at JSON in some detail, and I decided to experiment with this.

Basic Querying of a Document

A JSON document is text that contains key-value pairs, with colons used to separate them, and grouped with curly braces. Arrays are supported with brackets, values separated by commas, and everything that is text is quoted with double quotes.

There are a few other rules, but that’s the basic structure. Things can next, and in SQL Server, we store the data a character data. So let’s create a document:
```
DECLARE @json NVARCHAR(1000) = N'
{
  "player": {
             "name" : "Sarah",
             "position" : "setter"
            }
  "team" : "varsity"
}
'
```
This is a basic document, with two key values (player and team) and one set of additional keys (name and position) inside the first key.

I can query this with the code:
```
SELECT JSON_VALUE(@json, '$.player.name') AS PlayerName;
```
This returns the scalar value from the document. In this case, I get “Sarah”, as shown here:

I need to get the path correct here for the value. Note that I start with a dot (.) as the root and then traverse the tree. A few other examples are shown in the image.

These show the paths to get to data in the document.

In a future post, I’ll look in more detail how this works.

SQLNewBlogger

After watching the presentation, I decided to do a little research and experiment. I spent about 10 minutes playing with JSON and querying it, and then another 10 writing this post.

This is a great example of picking up the beginnings of a new skill, and the start of a blog series that shows how I can work with this data.
November 30, 2020