Unit testing with tSQLt on computed columns

Question

I’ve been writing some tSQLt database unit tests (via Red Gate SQL Test) on procedures which call tables containing (persisted) computed columns recently, and note that if I use the FakeTable SP, I find that the computed columns are not populated (they evaluate as null). The computed column is key to the test, so I can't just ignore the column in the test, and I'd rather not duplicate the logic.

I'm evaluating the results using the tSQLt.AssertEqualsTable SP, and so I want to make sure the column values are the same in both.

In practice, I’ve worked around this by not using FakeTable, but by using a (partial) rollback transaction statement at the end of the test (per the blog post at http://sqlity.net/en/585/how-to-rollback-in-procedures/) or explicitly deleting the test values.

I'm sure there must be a better way of coding this test, and would welcome any suggestions.

Cirrhosis

Answer 1

You should separate the logic in the computed column from the logic in the procedure when testing. The procedure is going to take the information in that column and act on it. The procedure should not care about the column being a computed column or a real column. That means, that in your test you can hardcode a value to put in that column. FakeTable makes that possible by turning any computed column into a real column.

In another set of tests you can (and should) test that the computed column is computed correctly. For that an addition to FakeTable is available. This preserves the computed property of the table. You need to set the @ComputedColumn parameter of EXECUTE tSQLt.FakeTable to 1. (http://tsqlt.org/user-guide/isolating-dependencies/faketable/)

Btw, you do not need to rollback anything in a test. tSQLt is taking care of that already. The logic described in the article you mentioned is only needed in your own procedure, if transaction management is a requirement of that proc.

Jairia

Thanks Sabastian, that's very helpful. I was trying to combine the two tests, but as you point out these should be different tests. – Cirrhosis

Answer 2

There is now a pre-release update to tSQLt available on the mailing list: http://groups.google.com/group/tsqlt

The pre-release contains features to preserve computed columns or defaults during FakeTable.

Examples:
EXEC tSQLt.FakeTable 'dbo.tst1', @ComputedColumns = 1;
EXEC tSQLt.FakeTable 'dbo.tst1', @Defaults = 1;

These will be ready for official release soon.

Warison