Voice of the DBA

Tag: SQL Test

Testing Sum By Month
I’ve been on a testing kick, trying to formalize the ad hoc queries I’ve run into something that’s easier to track. As a result, when I look to solve a problem, I’ve written a test to verify that what I think will happen, actually happens.

The Problem

I saw a post recently where someone wasn’t sure how to get the sum of a series of data items by month, so I decided to help them. They asked for a year number, a month number, and a total, so something like this:

Year   Month   Sales

2012       1   1500.23

2012       2   1480.00

2012       3   1945.00

…

2015       7   8933.11

They mentioned, however, that the had sales data stored as an integer. Not as 201201, but as 1, 2, 3, with a base date being Jan 1, 2012. That’s strange, but it’s a good place to write a test.

I like to start with the results, since if I don’t know the results, how can I tell if my query works? Let’s get a test going. I’ll start by created my expected results. I’ve come to like using temporary tables, and limited data. I also like to test some boundaries, so Iet’s cross a year.
```
CREATE PROCEDURE [tArticles].[test sum of sales by month for multiple months]
AS
BEGIN
-- Assemble
CREATE TABLE #Expected (
  yearnum INT
, monthnum TINYINT
, salestotal NUMERIC(10,2)
)

SELECT *
INTO #actual
FROM #Expected AS e


 
INSERT INTO #Expected
    ( yearnum
    , monthnum
    , salestotal
    )
  VALUES
    ( 2012, 11,  2500.23 )
,   ( 2012, 12,  2200.15 )
,   ( 2013, 1,  2656.75 )
```
I like to create the actual results table here as well, which allows me to then easily insert into this table from a procedure as well as a query. In this case, I’ll use a query, but I could use insert..exec.

Once I have results, I need to setup my test data. In this case, I’d probably go grab the rows from a specific period and put them in a temp table and use Data Compare to get them. Or make them up. It doesn’t matter. I just need the data that allows me to test my query.
```
EXEC tsqlt.FakeTable @TableName = N'MonthlySales';

INSERT MothlySales
  VALUES
    ( 11, 1000.00)
,   ( 11, 1500.23)
,   ( 12, 2200.15)
,   ( 13, 1000.00)
,   ( 13, 1656.00)
,   ( 13, 0000.75);
```
I don’t try to make this hard. I use easy math, giving myself a few cases. One, two, three rows of data for the months. If I think this isn’t representative, I can add a few more. I don’t try to be difficult, I’m testing a query. If I had rows that might not matter, or I wanted to test if 0 rows are ignored, I could do that.

Now I need a query. Something simple, a SUM() with a GROUP by is needed. However I need to also change 11 into 2012 11, so that’s an algorithm.

An easy way to do this is start with a base date. I’d prefer this is in a table, but I can do it inline.
```
INSERT #actual
SELECT 
 yearnum = DATEPART( YEAR, DATEADD( MONTH, datenum, '20120101'))
 , MONTHNUM = DATEPART(MONTH, DATEADD( MONTH, DATENUM, '20120101'))
 , SALESTOTAL = SUM(ms.salesamount)
  FROM dbo.MonthlySales AS ms
 GROUP BY 
 DATEPART( YEAR, DATEADD( MONTH, datenum, '20120101'))
 , DATEPART(MONTH, DATEADD( MONTH, DATENUM, '20120101'))
ORDER BY 
  DATEPART( YEAR, DATEADD( MONTH, datenum, '20120101'))
 , DATEPART(MONTH, DATEADD( MONTH, DATENUM, '20120101'))
;
GO
```
I’ll insert this data into #actual, which tests my query.

The final step is to assert my tables are equal.
```
-- Assert
  EXEC tsqlt.AssertEqualsTable
    @Expected = N'#EXPECTED', 
    @Actual = N'#actual', 
    @FailMsg = N'The calculations are incorrect';
  
```
The Test

What happens when I execute this test? I can use tsqlt.run, or my SQL Test plugin.

In either case, I’ll get a failure.

When I check the messages, I see the output from tSQLt. In this case, none of my totals seem to match.

What’s wrong? In my case, I’m adding the integer to the base month, but that means a 1 means 2012 02, not 2012 01. I’m a month off. Let’s adjust the query.
```
-- Act
INSERT #actual
SELECT 
 
 yearnum = DATEPART( YEAR, DATEADD( MONTH, datenum, '20111201'))
 , MONTHNUM = DATEPART(MONTH, DATEADD( MONTH, DATENUM, '20111201'))
 , SALESTOTAL = SUM(ms.salesamount)
  FROM dbo.MonthlySales AS ms
 GROUP BY 
 DATEPART( YEAR, DATEADD( MONTH, datenum, '20111201'))
 , DATEPART(MONTH, DATEADD( MONTH, DATENUM, '20111201'))
ORDER BY 
  DATEPART( YEAR, DATEADD( MONTH, datenum, '20111201'))
 , DATEPART(MONTH, DATEADD( MONTH, DATENUM, '20111201'))
```
Now when I run my test, it passes.

Why Bother?

This seems trivial, right? What’s the point of this test? After all, I can easily check this with a couple quick queries.

Well, let’s imagine that we decide to move this base date into a table, or that we alter it. We want our queries to continue to work. I can have this test as part of an automated routine that ensures this test will run each time the CI process runs. Or each time a developer executes a tsqlt.runall in this database (shared or populated from a VCS). I prevent refactoring queries.

More importantly, I can take results and alter them first, say if someone decides to change this to a windowing query. I could plug a new query in the test (or better yet, use a proc and put that call in the test) , and if I change code, I can verify it still works.

Write tests. You need them anyway, so why not formalize them? The code around this query, mocking test data, is something I do anyway, so this gets me a few more minutes to verify that the code works. I can tune the query, alter indexes, perf test, and be sure that code is still running cleanly.

http://www.sqwhere lservercentral.com/Forums/Topic1716471-1292-1.aspx#bm1716535
October 6, 2015
tSQLt – SQLCop – Checking Naming Conventions
I’ve been using tSQLt a bit to do some testing and one of the things I’ve tested is standards for code. I’ve been using a framework on top of tSQLt called SQLCop. These are a series of tests written to look for specific things. One of the items I do check is for sp_ named procedures. I’ve mostly gotten out of the habit of doing this, preferring spProcName, but at times I make a mistake in typing. This catches those simple errors.

Using SQL Cop

You can Download the SQLCop tests and install them in your database after you’ve setup tSQLt. If you are using SQL Test, then you also get the SQLCop tests installed when you add the framework to a database. For me, I see the tests in the SSMS plugin.

There are a lot of tests, but in this piece, I’ll look at the Stored Procedures Named sp_ test.

If I edit the test, I see it’s fairly simple code. I’ve included it here.
```
USE [EncryptionPrimer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SQLCop].[test Procedures Named SP_]
AS
BEGIN
    -- Written by George Mastros
    -- February 25, 2012
    -- http://sqlcop.lessthandot.com
    -- http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_
    
    SET NOCOUNT ON
    
    Declare @Output VarChar(max)
    Set @Output = ''
  
    SELECT    @Output = @Output + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)
    From    INFORMATION_SCHEMA.ROUTINES
    Where    SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'
            And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'
            AND ROUTINE_SCHEMA <> 'tSQLt'
    Order By SPECIFIC_SCHEMA,SPECIFIC_NAME

    If @Output > '' 
        Begin
            Set @Output = Char(13) + Char(10) 
                          + 'For more information:  '
                          + 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_'
                          + Char(13) + Char(10) 
                          + Char(13) + Char(10) 
                          + @Output
            EXEC tSQLt.Fail @Output
        End 
END;
```
This code looks at the meta data in the database for an routines, stored procedures, that start with sp_ as part of their name. If any results are returned from the query, the IF statement will be true and the @output will be returned as part of the tSQLt.Fail call.

Using the Test

Let’s write a stored procedure. If I do this:
```
CREATE PROCEDURE spLetsTestThis
AS
    BEGIN

        SELECT TOP 10
                e.EmployeeID
                ,   e.EmpTaxID
                ,   e.FirstName
                ,   e.lastname
                ,   e.lastfour
                ,   e.EmpIDSymKey
                ,   e.EmpIDASymKey
                ,   e.hashpartition
            FROM
                dbo.Employees AS e;

        RETURN 0;
    END;

GO
```
This is a simple procedure. I wrote it, execute it a few times and be sure it’s what I want. I’ve done basic testing, not let’s check it before I commit it to VCS.

The easy way to execute all the SQLCop tests is to right click them in SQL Test and execute them. I can also use T-SQL to run tests. However since I just want to show this one, I’ll right click it and select "Run Test".

This runs the test selected. I can also run an entire class, or all tests, but clicking in the right spot. In this case, the test passes and I see a green mark.

Now let’s write a new procedure:
```
CREATE PROCEDURE sp_GetArticles
AS
    SELECT  *
    FROM    dbo.Articles

GO
```
This is a bad procedure for a variety of reasons, but let’s execute my test. I see it fail, and a red mark appears next to my test.

In this case I also get a window from SQL Test popping up with more details. This contains the output from the test, which is also inserted into a table by the tSQLt framework.

Note that there is a URL with more information on this particular test. That is a part of the SQL Cop test code above. I could easily replace this with something particular to my environment if I chose.

At this point, I can rename the object, drop and recreate it, etc. to correct the issue. However running this test helps me to be sure I’ve gotten good code into the VCS. If I have this also run as a part of a CI process, it then prevents bad code from other developers appearing.

Meeting Standards

There are all sorts of SQLCop tests, and I’ll write about more, but this is an easy one to implement to prevent a bad practice in your coding by a team of developers. Allowing each developer to test themselves, as well as an overall check by some CI process means that our code quality improves.

If I have other standards, I can even write my own tests to enforce them, which I’ll do in another piece.

Downloads
February 12, 2015