I'm part of a team at Red Gate that has worked in collaboration with the open source tSQLt unit testing framework developers to release a preview build of what we're calling SQL Test, a SQL Server test runner tool integrated into SSMS.
One idea that was banded about was to distribute SQL Test with a small selection of database checks that would be valuable to database developers who are trying to adhere to best practices. I'm thinking of maybe a script to identify objects that haven't got the MS_Description extended property defined, or a check that CLR isn't enabled on the server. As I'm not a SQL Server developer or a DBA by trade, I'm stuggling to determine which checks are most useful to the SQL community, and whether these checks can be easily expressed in SQL and therefore are candidates for me to ship them as pre-defined tests in SQL Test.
I'd really appreciate any help from the community to suggest as answers to this question what you believe these checks should be, and if possible, supply me with a SQL fragment that you're happy to be made available out of the box with SQL Test. Please vote the checks that you like up so we get an idea of what's more popular.
Many thanks for your help!
I'd say it depends what you're trying to demonstrate - perhaps you need to produce a sample "unit" to test with them (possibly use the AdventureWorks database?)
I'd also suggest a couple of different types. For example, you've got tests like 'does it conform to our naming convention and do the parameters conform' but you've also got tests that check whether the expected output is generated from a given module (SP, function, etc), i.e. the traditional 'unit' tests. I think the former have value, but the latter may be better to demonstrate the SQL Test product. I'd include some of each type, personally.
I do agree with WilliamD though - tests on the DBMS environment seem to be an unusual choice for demonstrating a unit test engine; however a single instance may be helpful for demonstrating how system tests can be done.
A simple example to check for stored procedures which start with SP_ :
CREATE PROCEDURE [AcceleratorTests].[test naming convention test for stored procs that start with sp_] AS
EXEC tSQLt.AssertEquals @Expected = 0, -- sql_variant @Actual = @ProcCount, -- sqlvariant @Message = N'There are procedures which do not conform to the naming convention as they begin with SP' -- nvarchar(max)
It's the central bit of code that looks for the name - so this could be extended to fit the organisation's naming convention.
You could also check (to use @fatherjack's idea) for SPs in the dbo schema with a code snippet such as :
-- Check for stored procs owned by dbo schema SELECT @ProcCount = COUNT(*) FROM sys.procedures AS p inner join sys.schemas s on p.schema_id = s.schema_id WHERE p.type = 'P' -- SQL Stored procs only - could be extended to cover CLR SPs and s.name = 'dbo'
Hope this helps to illustrate.
An updated example to output the names of any test failures:
ALTER PROCEDURE [AcceleratorTests].[test naming convention test for stored procs in the dbo schema] AS
EXEC tSQLt.AssertEquals @Expected = 0, -- sql_variant @Actual = @ProcCount, -- sql_variant @Message = @Message -- nvarchar(max)
From functions, stored procedures and views I'd want to be able to confirm column names and data types stay the same (order doesnt matter).
I'd want to enforce naming conventions (such as not beginning with sp_...)
I'd want to (optionally) enforce no "dbo" schema objects
answered Nov 30 '11 at 06:05 AM
I think it's a generally good practice to have a clustered index on every table in the database (there are exceptions, but exceptions are exceptional). Here's a TSQL statement that will show how many, if any, there are in a database:
For extra credit (and because I'm learning the tool) I went ahead and created this test in SQL Test
answered Nov 30 '11 at 10:59 AM
Grant Fritchey ♦♦
It might be helpful if EXEC tSQLt.Run 'AcceleratorTests'; did return a result set so it could be output to a table for further reporting. Possibly in the format: No, TestCaseName, Result, Message (instead of raising error), StartTime, EndTime...just a thought. My apologies if this is currently possible--haven't read all of the documentation.
also wondering if it makes sense for AssertEqualsString to take another optional parameter: Collation, in case you want to compare as binary or something other than the current default.
Brent Ozar's Blitz test really covers the DBA Server stuff. I don't really see that it is worth duplicating what he's already done. Besides which, he actively maintains the stored procedure and gives a wealth of documentation to support it all. (Use it!!!)
As far as database development goes, there are a lot of code smells that are worth putting into SQL Test. A while back, I got a whole lot of these listed in my blog...
... My favorite is the CASE statement without an ELSE. OK, it is valid, but it is an accident waiting to happen.
As these were community contributions gathered from Twitter, I'll list them here, with greatful thanks to the original contributors