x

Valuable database checks sought for SQL Test

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][2], 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!

[2]: http://www.red-gate.com/products/sql-development/sql-test/
more ▼

asked Nov 29 '11 at 03:30 PM in Default

David Atkinson gravatar image

David Atkinson
317 12 18 21

Maybe I'm misunderstanding the idea of SQL Test, but aren't the things you are suggesting something to do with system configuration management as opposed to unit testing sql code?

I am "pro" both of these things, just don't understand the reasoning behind packing this sort of test into a tool designed for a different job.
Nov 30 '11 at 04:21 AM WilliamD
Yes and no. The framework we use, tSQLt, is designed for TDD Unit Testing. However, our vision is that SQL Test could be used (in a future release) to kick off all sorts of testing including Static Code Analysis, Code Smells, Performance Testing, Server configuration checks, etc. If we can use the tSQLt framework as it is now to kick off best practices checks, I see this as a great way of providing some value out-of-the-box.
Nov 30 '11 at 07:28 AM David Atkinson
Excellent, that sounds like it could be a really useful tool then. I shall put my thinking hat on and see what I can come up with.
Nov 30 '11 at 08:14 AM WilliamD
This is an aside, but since you mention CLR, I don't think a check for that would be overly useful. In the servers I manage, I want it enabled if and only if CLR is used on that server, and that is completely server by server.
Nov 30 '11 at 11:42 AM TimothyAWiseman
@David After playing with this add-in for a while, I will say, I like it but will probably use it for something other than its intended purpose. Instead of unit testing, this framework extends nicely to Data Quality Assessment tests. I suppose in a perfect world we have clean data in our dbs but unfortunately I don't live in that world. Eg similar to your search for the god particle, I have some geometry types where all shapes and points should not extend outside of an arbitrary polygon. Using these tests I can call STContains on the data to identify offending values. Pretty much any data quality rules can be modeled with this. Thank you.
Dec 01 '11 at 10:16 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

9 answers: sort voted first

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, -- sql_variant @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 ;

more ▼

answered Nov 30 '11 at 06:06 AM

Dave_Green gravatar image

Dave_Green ♦
3.6k 4 7

SQL Test already somes with a sample database that allows you to experience how TDD unit testing might work in SQL Server. What I'm hoping is to extend SQL Test's remit to also cover different types of testing. Naming convention checks would be good. Do you think this would be easily expressed in SQL?
Nov 30 '11 at 07:31 AM David Atkinson
These sort of checks are fairly easy to express in tSQL - I've edited my example above to implement a couple of simple examples. Let me know if these aren't clear. I've avoided mandating much in the way of a schema as these will probably differ signifanctly between organisations; an example might be that the object name should use CamelCase, or not have underscores in it. This is only an example though!
Nov 30 '11 at 01:43 PM Dave_Green ♦
Thanks for this! These are very clear. I guess the ideal script would also output the offending objects so the user can act on the feedback.
Nov 30 '11 at 01:59 PM David Atkinson
Hmm, good idea. I've edited my answer to add a new example to show one way of accomplishing this, outputting the object names which failed in the message.
Nov 30 '11 at 02:27 PM Dave_Green ♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 30 '11 at 06:05 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Sounds good. Do you happen to have SQL scripts that test for this?
Nov 30 '11 at 07:29 AM David Atkinson
(comments are locked)
10|1200 characters needed characters left

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 ;
more ▼

answered Nov 30 '11 at 10:59 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Nov 30 '11 at 05:46 PM

Scot Hauder gravatar image

Scot Hauder
5.9k 13 15 18

(comments are locked)
10|1200 characters needed characters left

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.][2]

... 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)
[2]: http://www.simple-talk.com/community/blogs/philfactor/archive/2010/11/22/95781.aspx
more ▼

answered Dec 01 '11 at 03:24 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

Phil - a wonderful list. I've love for everyone to identify which code smells in this list are the most important, and ideally for a SQL fragment to be made available to test for it! This is perfect material for a default set of 'best practice' checks.
Dec 01 '11 at 05:07 AM David Atkinson
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x61
x10

asked: Nov 29 '11 at 03:30 PM

Seen: 2974 times

Last Updated: Nov 29 '11 at 03:37 PM