Voice of the DBA

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.