Voice of the DBA

Tag: testing

tSQLt in Azure SQL Database

I was excited to hear about the v12 Azure databases supporting CLR assemblies. Mainly because I’ve been doing testing work with tSQLt and wanted to run tests in an Azure database.

I upgraded a SQL Azure database to v12 and connected in SSMS. My first step was to open the tSQLt file.

I had the file open, connected to Azure. This was the easy part. Now I need to actually send the batch across and compile the code:

My next step was to execute it. However that didn’t work as well as I expected.

There are multiple errors here, but it’s possible that one error causes others. I pinged Sebastian Meine, the creator of tSQLt about the External_Access and he noted there was only one method that needs it.

So I decided to make a change. First, a search.

That got me the assembly installation.

I decided to try and change this to something that’s contained inside the database. Since Azure is a bit of a black box, I thought safe was the way to go.

With that change made, I compiled the entire file again. This was the only change I made.

That’s cool, but does it work? I connected in Object Explorer and then opened SQL Test. I saw my database, and I could create a test.

However I got an error on the connection and creation of a procedure.

Even from Object Explorer, I refreshed the procedures, but got this:

This appears to be a SQL Server 2014 RTM error. Actually I was on a CU, but not SP1. It is supposed to be corrected in SP1.

However the procedure was created, and I could alter it.

More importantly, I can execute it.

If I change the code.

Now it fails.

It appears that tSQLt can now work in v12 databases in Azure, so start adding those unit tests to your database projects.

August 27, 2015
Unit Testing in Philadelphia
I’m helping teach a pre-conference session on Friday, June 5, 2015 at the Microsoft office in Philadelphia. This is an all day, paid for event, that looks at how you can use a framework to write unit tests for your T-SQL code. We’re the day before SQL Saturday #390 in Philadelphia

I really believe in testing, and am trying to advocate for it in many places. I’ve delivered a testing session that has been well received at quite a few events and this is the first time I’m trying a full day training class.

I am working with Sebastian Meine, the founder of tSQLt, to present the class. We’ve got a busy outline, looking at a variety of ways that you can write tests and use them to find problems in code. Here’s what we’re covering.
- Introduction to Unit Testing
- What is tSQLt?
- Your First Test
- Executing Tests correctly
- Effective use of Assertions
- Separation of Concerns
- Testing Exceptions
- Test Case Heuristics
- Dealing with Test Data
- Other Types of Testing
- How Unit Testing fits into your Development Process
At the end of the class, you should have some good ideas on how to build and structure tests in your own environment and be ready to start testing on Monday.

I hope to see you there, and register today if you want to learn more about unit testing in SQL Server.
May 22, 2015
Refactoring Mistakes Are Why We Write Tests
I wrote a short piece the other day trying to show how one can use tSQLt to test code. It’s a simple test built against a user defined function. It works well and when the test is run, it passes.

Here was my code:
```
ALTER function [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) )
returns int
as
    begin
      declare
        @ret as int = 1
      , @i as int = 1;
      while @i <= len(@value)
        begin
          if substring(@value, @i, 1) = ' '
            begin
              set @ret = @ret + 1;
            end
          set @i = @i + 1;
        end
      return @ret / 250;
;    end
```
Someone in the comments pointed out that we can more efficiently refactor this code to :
```
ALTER FUNCTION [dbo].[calculateEstimateOfReadingTime] 
( 
    @value varchar(max) 
)
RETURNS int
AS
BEGIN
RETURN (
    SELECT LEN(@value) - LEN(REPLACE(RTRIM(@value), ' ', '')) + 1
)
END
```
However when I run the test, I get these results:

That’s not good, but that’s why we test.

I could easily see someone refactoring the code, finding a more elegant method of rewriting this code and after running some quick tests, they check this in to source control (hopefully) and maybe deploy it to production. Hopefully QA catches this, but wouldn’t we want to notice this in development?

The refactored code misses a divide by 250.

Write tests, use them to catch mistakes. These simple ones slip through at times and are what make deployments really, really stressful.
May 20, 2015
Why Test Table MetaData Tests with tSQLt

I wrote at SQLServerCentral about using tSQLt to check table metadata. In essence we are testing the API of our table. However, since the table could change, and may need to, what’s the value of having a test fail if the table changes?

In my mind, I don’t necessarily want to have table structure tests for all my tables. After all, developers need to have flexibility to work with and change tables in our applications. If it’s a pain for a developer to change every table, because a test fails and they have to go change the test, that’s an issue.

There’s also the problems of a developer changing a table, changing the test, and then having everything pass, without passing along information that a schema change was made.

I would limit the API tests for a metadata to those tables that are important, with the caveat that anytime someone fails a metadata test, they need to inform the team.

But Steve, isn’t every table important?

Yes and no. Certainly all tables should be important to the application in some way, but really many of them are contained in the application. If changes are made, it’s not necessarily a problem to change other objects to catch up to the table change. However, some tables may cross teams or applications and they are an issue.

As an example, I have lots of tables in the SQLServerCentral database.

If the Blogs table, or the Articles table changes, then we need to alter stored procedures and possible ASP.NET code for our application. In fact, in this list, pretty much all of these tables could be changed by a developer without a large impact, assuming they’re going to look at the other objects or code affected.

However the table highlighted, the emails table, along with a few others, are important. These tables not only support SQLServerCentral, the web app, they are also called by our emailer process, which is a completely separate application. In essence, these tables are the opposite of a microservice. They’re shared.

If someone wants to change the Emails table, I want to be sure that others are informed. In fact, I might choose to include a note in the test header that various groups need to be informed or that the table affects another application. In that case, before a developer went to change the test, they might at least have a chance or noting this has far reaching implications.

It’s not a perfect solution, but it does help. The other thing I could do is limit access to metadata tests for various tables/views and merely call these tests in a CI, or other automated, process. That way failures would be public, and a variety of people could be informed, preventing a developer from making changes without a discussion.

As I mentioned, I wouldn’t do this for all tables. In fact, I’d limit this to particularly sensitive tables that might require lots of rework if they were changed. We want to speed development, and ensure code works, not slow developers down.

May 18, 2015