Voice of the DBA

Author: way0utwest

Actively Choosing Compatibility

SQL Server databases have had a compatibility level for a long time. This is a setting that enables the database to process code as if it were a particular version. The levels go from 80 (SQL Server 2000) to 160 (SQL Server 2022). Each time (almost) a new version of SQL Server has been released, there has been a new compatibility level.

However, not all versions can support all compatibility levels. For example, my SQL 2022 instance can support levels back to 100 (SQL Server 2008). If I wanted to get a database to act like a version older than 2008, for example, 2005, I would need to install a SQL Server 2012 or older instance. There is a table of engine versions and supported compatibility levels on the ALTER DATABASE Docs page.

When you create a new database, by default, it is at the current compatibility level. However, if you upgrade a database, the level might not change. There are some limits to which versions are supported, so an upgrade might change your database.

An administrator might choose to keep an older level for compatibility purposes. Perhaps your code has an identifier that is now a keyword. Perhaps you expect some code to behave a certain way. However, not all changes are protected by the compatibility level. Most of the time, an administrator must manually change this, which is something that can slip through the cracks. If you don’t change this right away, likely it isn’t going to change.

There was an interesting post from Brent Ozar recently that explained a bit about compatibility levels in a SQL Server database. I wonder how many people actually actively choose a level or they just accept the default level for that instance. Brent gives some advice in the post, and his recommendations vary a bit, depending on whether you are happy with the system or not. He also recommends measuring your system and then evaluating a change. Especially if this is a database for vendor software.

I don’t think changing or updating this setting is a priority, but I also think that being aware of when your level doesn’t match the instance and documenting this is important. At some point, through many upgrades, you might find your level isn’t supported any longer. Then your database might have immediate issues. A good monitoring system can let you know when you have mismatches that can be evaluated when there is extra time to clean up or test changes to the system.

Each version of SQL Server adds new features, like the changes for Intelligent Query Processing. In general, we want to take advantage of these if we can. However, not all workloads respond positively, so as Brent mentions, you need to test and evaluate your workload. Hopefully, you have a clear “things are better” or “things are worse” when changing levels. When you get some queries that perform better and some worse, then you have some choices to make. Often the default is “do nothing,” which may or may not be the best decision, but the devil you know is sometimes easier to deal with than the one you don’t know.

That’s fine but consciously make that choice. Keep an eye on your system and don’t just accept defaults, whether those are from Microsoft or the ones you’ve left after an upgrade. Actively manage your systems to get the best performance you can for clients.

Steve Jones

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

June 14, 2023
Across the Water Again

I’m in the UK again, for my second trip this year. This time I have no commitments for speaking or presenting anything. I’m in town for a Marketing get-together and our internal Level Up conference. Nice to be here without being responsible for anything, which is rare.

It’s a relatively quick trip for me. No Mrs. way0utwest , but I was traveling last week and next week I take time away to head to Orlando and AAU Girls National Volleyball Championships, so this is slotted around those items.

It’s a relatively busy week, so no sure there will be any blogging for now, but if I come up with something interesting and have some time, I might. I’m focusing on taking a physical paper notebook and pen and trying to do most of my thinking and learning that way.

June 12, 2023
Blogging for the Tech Professional at Denver Dev Days
For those that attended my talk at Denver Dev Days, here are the slides: BloggingFortheTechPro.pptx

A couple interesting questions that I need to add to the deck.

What do you recommend for a student? (or someone early in their career)

This could also apply to someone changing careers or fields. If you don’t have experience, does blogging help?

It does. A blog is your portfolio and it’s a good way to showcase what you are learning, have learned, what you are interested in. This gives you some way to show “experience” even if you don’t have any.

Build a project, explain how you learned something, document your journey. I might think about a wider set of topics rather than deeper on one here, as it’s hard to know what you want to do early in your journey. It’s also a time when you don’t want to limit opportunities, so showcase a lot of different things you’ve worked on.

How do I get better at blogging?

Build a network of people you trust. Let them see drafts, get feedback, and incorporate the feedback into your writing. I would do that in two different ways:
1. Am I correct in my technical details? To do this, get quality feedback from a subject matter expert. Think senior skilled person evaluating your work here.
2. Am I explaining things well? Get feedback from anyone on spelling, phrasing, grammar, and flow. Think English teacher feedback here.
How do I justify the time to blog when I’m busy at work?

This is an investment in your career. Doctors, lawyers, accountants, etc. need to work on their careers outside of work. Chefs, mechanics, plumbers do this as well.

Technical people can do this. Take 15-30 minutes a week to do this. Over time, it’s a great way to add richness to your resume and stand out from others.

Do you blog at work?

In general, I make notes and sketches of things at work. 3-5 sentences, some bullet points. Then I would spend time outside of work fleshing out my posts. For examples of how long I spend on things, check out the SQL New Blogger series. If you’re starting out, double my times for yourself.
June 9, 2023
Getting AI Written Unit Tests
As a part of my AI experiments, I decided to ask CoPilot to write some unit tests. Here is what happened.

The Prompt

To get started, you enter a prompt as a comment and put the cursor on the next line. Then I can open the CoPilot completions panel. Here was my prompt:
```
-- for an orderheader table with orderdate and ordertotal columns
-- write a tsqlt test to ensure that the orderdate is not null
-- and that the ordertotal is greater than zero
```
Once I completed this, I looked at the results.

The Solutions

The first few solutions weren’t very helpful. I’m not even sure what was the thinking for 1 and 2. Three at least starts to get me to think about different cases.

Solution 6 started to have promise:

Then solution 8 was here. It opens with a short summary of what to do. Not terribly useful here, as this type of structure is something that a developer learns once and then remembers.

Suggestion 8

The solution was a mix of code and text, which I assume is assembled from somewhere on the web. Leaving aside the problems of Intelletual Property, here’s what I saw. All the results are formatted as code here.
```
CREATE PROCEDURE [test orderheader columns]

AS

BEGIN

    -- Assemble

    -- Act

    -- Assert

END

GO

```

The test should pass if the column is not null and the total is greater than zero.  The test should fail if the column is null or the total is less than or equal to zero.

OP 2019-09-18: The following is a solution that I put together:

```

CREATE PROCEDURE [test orderheader columns]

AS

BEGIN

    -- Assemble

    DECLARE @columnName sysname = 'orderdate'

    DECLARE @sql NVARCHAR(MAX) = N''

    -- Act

    SELECT @sql = N'SELECT ' + @columnName + N' FROM orderheader'

    EXECUTE sp_executesql @sql

    -- Assert

    EXECUTE tSQLt.AssertEquals @Expected = 1, @Actual = @@ROWCOUNT

END

GO

```

The assert statement will fail if the column is null.  If you want to assert that the column is not null, you can use the following:

```

EXECUTE tSQLt.AssertNotEquals @Expected = 0, @Actual = @@ROWCOUNT

```

If you want to assert that the column is not null and the value is greater than zero, you can use the following:

```

EXECUTE tSQLt.AssertNotEquals @Expected = 0, @Actual = @@ROWCOUNT

AND @Expected = 1, @Actual = (SELECT COUNT(*) FROM orderheader WHERE orderdate > 0)
```
```
That looks interesting. It’s a set of things I need, but not quite ready for us. However, it has some structure that I can use.

Did It Help?

Yes and no.

There is code I can use. The problem for me is that I need to read through and copy/paste a bunch of things together. I also need to then decide what my test includes, and write more code that might make this work for my particular system.

Supposedly we’ll get CoPilot for our internal systems at some point, where there isn’t a danger of feedback being sent to the public model. I think this could learn to be better and save me coding time if it could see my schema and use that to inform and guide the code it returned for me.

Right now it’s not very helpful, and certainly at this time, it seems like it slows me down. However, maybe some of that is me learning to use it more and some is the model getting smarter (or more tailored).

Still, an interesting experiment.
June 9, 2023