Valuable database checks sought for SQL Test

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.

EDIT

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

BEGIN

DECLARE @ProcCount INT

SELECT  @ProcCount = COUNT(*)
FROM    sys.procedures AS p
WHERE   p.type = 'P' -- SQL Stored procs only - could be extended to cover CLR SPs 
AND UPPER(LEFT(p.name,3)) = 'SP_' -- User Stored Procs shouldn't have SP_ at the start of their name.  

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)

END ;

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.

EDIT

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

BEGIN

DECLARE @ProcCount INT,@ProcNames nvarchar(max), @Message nvarchar(max)

-- 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'
IF @ProcCount > 0
BEGIN
SELECT  @ProcNames = COALESCE(@ProcNames+', ','')+quotename(s.name)+'.'+quotename(p.name)
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'
END

SET @Message = N'There are procedures which are in the dbo schema. These are: '+@ProcNames

EXEC tSQLt.AssertEquals @Expected = 0, -- sql_variant @Actual = @ProcCount, -- sql_variant @Message = @Message -- nvarchar(max)

END ;

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

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:

SELECT  COUNT(*)
FROM    sys.tables AS t
        LEFT JOIN sys.indexes AS i
        ON t.object_id = i.object_id
           AND i.type = 1
WHERE   t.type = 'U'
        AND i.TYPE IS NULL ;

For extra credit (and because I'm learning the tool) I went ahead and created this test in SQL Test

ALTER PROCEDURE [AcceleratorTests].[test for clustered indexes]
AS 

BEGIN

    DECLARE @TableCount INT

    SELECT  @TableCount = COUNT(*)
    FROM    sys.tables AS t
            LEFT JOIN sys.indexes AS i
            ON t.object_id = i.object_id
               AND i.type = 1
    WHERE   t.type = 'U'
            AND i.TYPE IS NULL ;

    EXEC tSQLt.AssertEquals @Expected = 0, -- sql_variant
        @Actual = @TableCount, -- sql_variant
        @Message = N'There are tables without a clustered index' -- nvarchar(max)

END ;

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.

Cheers,

Scot

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...

Listing common SQL Code Smells.

... 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

Use of deprecated syntax such as *= - see SR0010: Avoid using deprecated syntax when you join tables or views  (Dave Howard)
Denormalisation that requires the shredding of the contents of columns. (Merrill Aldrich)
Contrived interfaces
Using VARCHAR and VARBINARY for datatypes that will be very small (size 1 or 2) and consistent - see SR0009: Avoid using types of variable length that are size 1 or 2
Use of deprecated datatypes such as TEXT/NTEXT (Dave Howard)
Datatype mis-matches in predicates that rely on implicit conversion. see SR0014: Data loss might occur when casting from {Type1} to {Type2} (Plamen Ratchev)
Using Correlated subqueries instead of a join   (Dave_Levy/ Plamen Ratchev)
The use of Hints in queries, especially NOLOCK (Dave Howard /Mike Reigler)
Few or No comments.
Use of functions in a WHERE clause. (Anil Das)
Overuse of scalar UDFs (Dave Howard, Plamen Ratchev)
Excessive ‘overloading’ of routines.
The use of Exec xp_cmdShell (Merrill Aldrich)
Excessive use of brackets. (Dave Levy)
Lack of the use of a semicolon to terminate statements
Use of non-SARGable functions on indexed columns in predicates (Plamen Ratchev)
Duplicated code, or strikingly similar code.
Misuse of SELECT *  -see SR0001: Avoid SELECT * in stored procedures, views and table-valued functions  (Plamen Ratchev)
Overuse of Cursors (Everyone. Special mention to Dave Levy & Adrian Hills)
Overuse of CLR routines when not necessary (Sam Stange)
Same column name in different tables with different datatypes. (Ian Stirk)
Use of ‘broken’ functions such as ‘ISNUMERIC’ without additional checks.
Excessive use of the WHILE loop (Merrill Aldrich)
INSERT ... EXEC (Merrill Aldrich)
The use of stored procedures where a view is sufficient (Merrill Aldrich)
Not using two-part object names (Merrill Aldrich)
Using INSERT INTO without specifying the columns and their order (Merrill Aldrich)
Full outer joins even when they are not needed. (Plamen Ratchev)
Huge stored procedures (hundreds/thousands of lines).
Stored procedures that can produce different columns, or order of columns in their results, depending on the inputs.
Code that is never used.
Complex and nested conditionals
WHILE (not done) loops without an error exit.
Variable name same as the Datatype
Vague identifiers.
Storing complex data  or list in a character map, bitmap or XML field
User procedures with sp_ prefix (Aaron Bertrand)
Views that reference views that reference views that reference views (Aaron Bertrand)
Inappropriate use of sql_variant (Neil Hambly)
Errors with identity scope using SCOPE_IDENTITY @@IDENTITY or IDENT_CURRENT- see SR0008: Consider using SCOPE_IDENTITY instead of @@IDENTITY  (Neil Hambly, Aaron Bertrand)
Schemas that involve multiple dated copies of the same table instead of partitions (Matt Whitfield-Atlantis UK)
Scalar UDFs that do data lookups (poor man's join) (Matt Whitfield-Atlantis UK)
Code that allows SQL Injection (Mladen Prajdic)
Tables without clustered indexes (Matt Whitfield-Atlantis UK)
Use of "SELECT DISTINCT" to mask a join problem (Nick Harrison)
Multiple stored procedures with nearly identical implementation. (Nick Harrison)
Excessive column aliasing may point to a problem or it could be a mapping implementation. (Nick Harrison)
Joining "too many" tables in a query. (Nick Harrison)
Stored procedure returning more than one record set. (Nick Harrison)
A NOT LIKE condition (Nick Harrison)
Not setting an output parameter for all code paths through a stored procedure, see SR0013: Output parameter (parameter) is not populated in all code paths
excessive "OR" conditions. (Nick Harrison)
User procedures with sp_ prefix (Aaron Bertrand)
Views that reference views that reference views that reference views (Aaron Bertrand)
sp_OACreate or anything related to it (Bill Fellows)
Prefixing names with tbl_, vw_, fn_, and usp_ ('tibbling') (Jeremiah Peschka)
Aliases that go a,b,c,d,e... (Dave Levy/Diane McNurlan)
Overweight Queries (e.g. 4 inner joins, 8 left joins, 4 derived tables, 10 subqueries, 8 clustered GUIDs, 2 UDFs, 6 case statements = 1 query) (Robert L Davis)
Order by 3,2 (Dave Levy)
MultiStatement Table functions which are then filtered 'Sel * from Udf() where Udf.Col = Something' (Dave Ballantyne)
running a SQL 2008 system in SQL 2000 compatibility mode(John Stafford)