Voice of the DBA

Category: Blog

Daily Coping 24 Apr 2020

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

Thank three people you’re grateful to and tell them why.

It’s always good to thank people when you can. The action shows good manners, but it also shows gratitude for others who have helped you. Perhaps in a way they are unaware of.

For me, I have many people that I am grateful for. Certainly my kids, both with their success, their insight into their lives and others that teaches me things, and their efforts to help me get through life. My kids are always willing to help, albeit not always happily.

There are others, but I’m fairly private here in who I thank. I’ve reached out to a few people to let them know, but the public ones I’ll recognize here are these three (of many).

Kalen Delaney (@sqlqueen) – I read her book early in my career, and met her at the very first PASS Summit in Chicago. She inspired me to learn more, grow my skills, and even begin writing.

Andy Warren (@sqlandy) – My fellow founder from SQLServerCentral and likely my closest friend across the last 20 years. We still talk almost every week, despite never living in the same city, and outside of my wife, he’s likely had the most influence on my in my life.

Allen White (@sqlrunr) – Outside of my son, I’ve run with Allen more than any other person on the planet. We’ve shared early morning runs, meals, and many hugs across the years. We’ve enjoyed conversations, debates, and learned from each other many times. I always appreciate his point of view, thoughts, and perspective, and I can sometimes hear his voice in my head, as I navigate life and the world.

There are many others, and I’ve reached out to some, but take a moment today and reach out to someone.

April 24, 2020
Live Stairways Day 1 Homework

I recorded my first class for the Live Stairways – Learning DAX and Power BI. My apologies for anyone that wanted to join. We had an error in the Zoom meeting, and I had to create a new meeting.

Some fits and starts, but still, an interesting first class, as I worked through Level 1. A few things I, and you, need to do to follow along. I’ll add this to the class course, but for now, some homework.

Homework

First, SQL Server.

If you haven’t installed SQL Server and SSAS, do that. You can get the developer edition from the SQL Server downloads page. I’m using SQL Server 2017, but 2019 should work. Set this up on a machine somewhere, and be sure to add SSAS.

I don’t have SSAS installed, so I’ll do a video and post on how to add that to your SQL Server installation.

Next, Power BI Desktop.

If you haven’t installed Power BI Desktop, go here and download it. It’s an easy, standard Windows install.

Last, AdventureWorks DW Version

The AdventureWorks scripts are on GitHub. You can download the install script and run this to set up the DW database.

That’s it for now. I’ll hold off on the SSAS project for now.

homework #1 – Install SQL Server and SSAS
homework 2 – AdventureWorks DW version
Homework 3 Power BI

April 23, 2020
SQL Prompt Fixes Always Have an Else
SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide you along and fix some bad code that your team might write out of habit.

SQL Prompt 10.1 released recently, and one of the recent fixes is for an issue that we’ve denoted as BP012. This is an issue where there is a CASE statement in code, but no ELSE has been defined. An example of this is shown below:
```
SELECT 
  oli.OrderLineItemKey
, oli.OrderID
, oli.qty
, CASE WHEN oli.qty < 10
         THEN oli.unitcost
     WHEN  oli.qty >= 10 AND oli.qty < 20
         THEN oli.unitcost * .05
     WHEN  oli.qty >= 20 AND oli.qty < 100
         THEN oli.unitcost * .1
     END AS UnitCost
, oli.linetotal
  FROM dbo.OrderLineItem AS oli
```
This is a problem because we often find some weird data being entered. You might think there will never be more than 100 ordered, but who knows. We should always have an ELSE clause, just in case.

Why? Here is why.

NULL is returned if you haven’t accounted for something.

If you hover over, you get the issue in a tip. If you put the cursor on the line, you get the lightbulb with the fixes on the side.

As with other fixes, you can click the light bulb and see the fixes.

When we click the top item, SQL Prompt will add an ELSE clause, with a placeholder and the cursor is here. You can start typing, and enter the expression or value that is appropriate.

This is another place where junior (or overloaded) developers may make a mistake. Having this in SQL Prompt allows this to be caught by the individual developer. Having this in SQL Code Guard (part of SQL Prompt and our automated build/deploy tools) and an automated build also means the code is evaluated automatically and this certainly doesn’t go to QA.

If you haven’t tried SQL Prompt, download an eval and see what you think. If you have it, upgrade and ensure you have all the code fixes.
April 22, 2020
Daily Coping 22 Apr 2020

I’ve started to add a daily coping tip to the SQLServerCentral newsletter, 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 look for the good in others and notice their strengths.

This has been a trying time, and the stress, the close quarters, the lack of variety, it’s made many of us cranky. Certainly it’s made me cranky at times.

Twitter is the worst, but all social media can test one’s patience. It doesn’t really matter which way you lean on an issue, there are plenty of people that will annoy you. Since social media is a bit of my job, I’m slightly trapped at times.

What I’ve done is to a) try to stop reacting, and b) appreciate the other point of view. I know most people with a strong opinion that differs from mine are not looking to make things worse. They often have a reason for their own view of the world, and I am trying to take a deep breath, respect that, and then think about things from their point of view. It’s helping.

When I can actually avoid reacting quickly.

April 22, 2020