Voice of the DBA

Tag: syndicated

SQL Prompt Fixes Poor Order By Coding
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 BP002. This is where someone writes this code.
```
SELECT arts.ArticlesID
      , arts.Title
      , arts.Description
      , arts.PublishDate
      , arts.URL
  FROM dbo.Articles AS arts
  WHERE arts.PublishDate > DATEADD(WEEK,-2,arts.PublishDate)
  ORDER BY 1
```
I don’t see this a lot from Microsoft stack developers, but PHP, Java, and some other languages will do this.

This has been deprecated, but it’s a poor practice as well. The dependency between the ordering and column list is not good. This is an easy place to make mistakes over time as code is refactored.

If I do this, SQL Prompt gives me a green squiggly line below the constant.

If I put the cursor here, I get a lightbulb in the sidebar. Clicking that will give you some options.

If I click the first item, this will give me a placeholder where I can choose a column. We could replace this with the first column in the column list, but if you detect this during refactoring, the first item from the column list might not be the one you want.

I can start typing and intellisense takes over.

It’s a small thing, but this is a one way that increases code quality for developers that might not know better.

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 15, 2020
Daily Coping 15 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 send a letter or message to someone you can’t be with.

I’ve actually been doing this on a lot of days. I’ve been reaching out through text, Facebook, or email to various friends, family, and colleagues that I would often see at different times during the year, but I haven’t seen in awhile. It’s a quick way to just touch base with some people and see how they’re doing.

I should just do this more often.

April 15, 2020
Daily Coping 14 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 find a way to get an extra 15 minutes of physical activity, but a fun way.

I miss volleyball. This is the time of year I’d be winding down the season. This would actually be my last full week of practice before a tournament this weekend and final evals next week. I coach, so I don’t do a lot, but I usually get in a short 15-20 minutes of play with the kids, as well as demonstrating things for them to learn.

My last practice was Mar 11. The last time I played was Mar 9, when I warmed up one girl before the last match, playing pepper with her.

My daughter is also stuck here, and she’s missing her spring season at college. I plan to try and get out in the riding arena and practice a bit with her tonight.

It will be a good release, though I still miss the team.

April 14, 2020
T-SQL Tuesday #125 Unit Testing
This month T-SQL Tuesday is hosted by Hamish Watson, quarantined down in New Zealand. Like me, he has a ranch, and kids, so he has space to move around a bit.

Hamish is a DevOps and testing advocate, just like me. His topic this month is to ask “So now that I have introduced unit testing – is it valuable for implementing with databases? “

I think it’s a great topic, and here is my take.

Database Unit Testing Documents

My big take is that database unit testing best documents what you, as a developer, think your part of the database code should do. You expect these things:
- The API a table will provide
- The way a CASE statement decides what to return
- The formulas you use for aggregation
- The manipulation a function uses to return a value
And more. Ultimately, we interpret a requirement, whether written down or told to us. We often may slightly mis-interpret what someone told us, or not think of certain cases. A test forces us to document the way we think our code behaves with certain data.

This isn’t perfect, as we still may not account for certain cases of data, we may have bugs, or we may interpret things incorrectly. However, the test documents what we think. When there is an issue, we can go look at the test and verify this behavior is what is expected. Or we can add a new test (or enhance ours) to handle the case we didn’t expect.

There are plenty of other reasons, but I believe that testing helps improve quality because it forces us to think, it documents our actions, and it helps ensure we don’t create regressions of code that already works.
April 14, 2020