tSQLt TRUSTWORTHY and CLR requirement

Question

The tSQLt test framework requires to set TRUSTWORTHY ON for the database, and to enable SQL CLR for the server.

Why are these required? Is it possible to achieve the same/similar functionality without a CLR object?

Dioptrics

The obvious answer is that some part of tSQLt consists of SQL CLR code that requires access to external resources, or unmanaged code. As for the 'is it possible', well, there's nothing stopping someone trying to make a SQL unit testing framework that runs in pure T-SQL, but I wish them luck... – Sophomore

Answer 1

The following features are completely implemented as CLR code and would be particularly difficult (and probably impossible) to do without CLR:

• CaptureOuput: useful for recording and validating output printed to the console
• SuppressOutput: used internally to help keep the console clean of warnings resulting from renaming objects (particularly in FakeTable)
• ResultSetFilter: necessary for capturing a specific result set produced from a stored proc that produces multiple result sets
• AssertResultSetsHaveSameMetaData: used to compare the meta data of two result sets
• NewConnection: allows an arbitrary SQL command to be executed in a new connection. Useful for testing when certain properties of the connection must be different. (If memory serves me correctly, this might be the sole reason for TRUSTWORTHY ON today).

Other than that, tSQLt uses the CLR internally to produce formatted output for very large strings, such as those that can be produced by comparing two tables; and for generating new object names when renaming objects (such as for FakeTable or SpyProcedure).

It is conceivable that tSQLt could be modified to remove these features and still function with its base feature set (with some limitations of course). However, as we look ahead as to what will be in future releases of tSQLt, it is very likely that there will be more done in CLR.

Thereat

Thanks Dennis. Would the asymmetric key approach mentioned by Ed (msdn.microsoft.com/en-us/library/ms345106.aspx) perhaps allow the TRUSTWORTHY ON requirement to be removed? – Dioptrics

Answer 2

TRUSTWORTHY ON is no longer required. See the release notes: http://tsqlt.org/748/tsqlt-v1-0-5873-27393-release-notes/

Pathos

Answer 3

If you want to use tSQLt, you probably don't have any option but to enable SQL CLR.

There is a way to get around the requirement to set the database as TRUSTWORTHY ON by creating an asymmetic key - see http://msdn.microsoft.com/en-us/library/ms345106.aspx.

If this isn't acceptable, there are other database unit testing tools which don't require objects to be created in the database - for example DbFit

Diplopod