Voice of the DBA

Author: way0utwest

A New Word: Emodox

emodox – someone whose mood is perpetually out of sync with everyone else around them, prone to feelings of naptime panic, heart-to-heart snark, or dance club pensiveness.

I had to look up pensiveness as I wasn’t completely sure what this means. It is deep serious thoughtfulness, which is something I certainly engage in at times.

In general, I am often in touch with what’s happening around me and I can be in sync with the moods and situation. I tend to be empathetic, and deep feelings from others cause me to react. Certainly I misread things, but I’m not out of sync.

Usually.

There are times when I experience this, and I’m very, very uncomfortable. With family, or with my wife, I can let them know. If I’m in other situations, I might just walk away and leave, or linger outside of whatever is going on around me. I can be snarky and snappy, which isn’t a great reaction, which is why I have learned to try and extract myself from situations when I feel this.

From the Dictionary of Obscure Sorrows

November 24, 2023
Having Data Modeling Standards

While working with a customer recently, they mentioned that they have certain standards for their objects. They require a PK, and it’s the name of the table with _PK added. They also have some standards, like CustomerName vs. CustomerNames for various data items. In fact, they have enough that they built a tool to scan their database code to ensure that changes to the QA and UAT environments adhere to these modeling standards.

I wonder how many organizations have formal standards. While I’ve often tried to set some naming guidelines, I often haven’t seen anything (or created anything) formal enough to build a tool around. I would like to, and I think it’s a good idea, but it’s often something that isn’t handled in advance.

One thing I do think is important is to use singular naming. It’s strange to me, as I’ve often thought of a table as a collection of Customers or Orders, but I get why singular makes more sense. I really started to embrace this more after a great design talk at one of the Denver Dev Days.

In the talk, the speaker talked about naming a table as an entity, but then including the entity name in columns. For something like a Customer table, we might have CustomerID, CustomerFirstName, CustomerLastName, CustomerStatus, CustomerPreferredContact, etc. There could be FKs in here, such as ContactID or AddressID, that linked to the entries in those tables, but in general, using a name like this was fairly unique for each column, and it helped anyone know where the data resided.

It was a lot of typing, and if you didn’t have a tool like SQL Prompt, that might be really annoying, but I found myself seeing this as a very logical and easy-to-understand structure that even inexperienced developers might be able to follow and mimic. That doesn’t mean there isn’t a need to understand how we want to model structures like names, addresses, and more, but it did seem better than having FirstName in some fields and fname in others. CustomerFirstName or EmployeeFirstName makes a lot of sense. It also prevents ambiguous columns in queries.

I do think that good data modeling standards are helpful in organizations, especially as we allow more and more people to make database changes and our teams grow larger. I would like to know how many of you reading this feel. Do you think strong data modeling standards are a good idea?

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

November 22, 2023
A SQL Prompt AI Experiment with Window Clauses

SQL Prompt has an EAP with some AI capabilities. I was asked to do some testing, and while I’ve done relatively little, I did find some time to play with this recently and decided to document what happened.

I completed this and then clicked the < back arrow to return through previous versions of the code, which also shows what the prompt was that produced this code.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

My Experiment

I wanted to write a windowing function to test something. Instead, I decided to ask SQL Prompt to do this for me. I connected to a database, hit ALT+Z, and then entered this prompt in the popup. You can see the code produced.

I only entered the prompt in the dialog. SQL Prompt wrote the highlighted code.

This wasn’t what I wanted, so I changed the prompt. You can see what I typed and what changed below.

Not bad, but not what I wanted. Next I asked to move this to the window clause. I got a window clause, but it wasn’t used in the OVER() clause.

Hmm, I tried again. Once I hit the play button, the AI does something and clears the prompt. That happened above, and this is the prompt I tried next:

It still didn’t work. Actually, it really didn’t work. I’m glad I have a back button.

Let me move on. I can fix that myself. Now I’ll change the column list.

That worked:

Can I add a second aggregate? I’ll try that.

That didn’t work. It changed my SUM to the COUNT I wanted. Interesting.

Thoughts

This is interesting in that it can help me structure queries, but knowing how to prompt them matters. I do like the back and forth, as I hit back above and then added to my prompt, which worked.

This doesn’t feel faster to me, but I’ve written a lot of SQL code. I could structure this stuff quickly and easily. In fact, I’d likely use a * in the column list and try to build the window clauses I need first, then go back and add aggregates. Normal SQL Prompt would help me, and I think it would be faster than the AI.

However, I also know the AI is new, and there are possibilities here, which might be easier than me playing with syntax and looking through suggestions, even with SQL Prompt.

I’ll keep playing and hopefully I’ll find this useful. Or at least useful enough that I can give good feedback to the team experimenting here.

November 22, 2023
T-SQL Tuesday #168–Roundup

Last week was the 168th T-SQL Tuesday, which I hosted. The invitation is here.

I didn’t get much of a chance to check out the posts as I was at the PASS Data Community Summit, but I came home and started to work through them.

This was the 8th one I’ve hosted, which makes sense as I’ve taken over managing the party from Adam Machanic and there have been a few places I’ve had to fill in for missing hosts. In any case, here’s the roundup. I’m going in order of the comments as I see them on the blog.

Rod writes about using LAG to rewrite older cursor code that summarizes data by period. for a 30x reduction in runtime. Plus, one less cursor in the world.

Aaron Bertrand has probably done most, if not all, of the T-SQL Tuesday parties. He’s been an expert in many aspects of T-SQL and I always look forward to reading his posts. In this one, he writes about a few different problems he’s solved with different window functions on the Stack Overflow database.

Deb the DBA was in Seattle with me last week, but she found time to give us a way she gets visibility into long running processes from an audit table. In this case she wraps a few window functions inside of a set of MAX() queries of the data.

Andy Brownsword a few relative queries that perform better with window functions. A good set of examples you might use in your work.

Hugo warned me he was going to write a long post. He did. Worth a read as he delves into the execution plans behind window functions.

My own post was on a change in SQL Server 2022 that makes it easy to re-use window definitions and not have to copy/paste them.

I believe Rob Farley has done every T-SQL Tuesday, and this month is no exception. In this case, he shows how to look at temporal table data.

Chad Callihan looks at Stack Overflow and how Lead() can find gaps.

And from Twitter, I caught Barney Lawrence’s post on using FIRST_VALUE, LAST_VALUE and NULLs. I don’t often see too many people looking at first_value() and last_value(), so I liked this one.

That’s it. If you want to host in 2024, I’ve still got some spots near the end of the year. Ping me and participate in a few of the other parties on your blog.

November 21, 2023