SQL unit testing is rising in popularity amongst database developers. The importance of Code Quality in Software Development has increased over the period of time. Everyone wants to follow coding standards and write clean code. However, to timely deliver a product to Business we end up in violating design principles, writing fewer unit tests, increasing code complexity and breaching best practices. This increases the overall Technical Debt of the application and has the potential to halt future development work by creating unplanned work.
There are a lot of tools available which can help you to build high-quality software by identifying vulnerabilities at an early stage. Application developers use tools like SonarQube and Analyzers to continuously inspect code quality. But what about database development? Wouldn’t it be awesome to have similar toolset available which can inspect your database code and identify problems with design, performance and security?
In this article, we will learn about SQL Unit Testing, Database Static Code Analysis, the benefits of using it in Database Development, SQL Cop and how to use it to identify anti-patterns in your database.
What is SQL Unit Testing?
Unit Tests helps you to independently verify a small functionality of your application – be it front end code or database code. Writing SQL Unit Tests is an integral part of the database lifecycle and gives more confidence to developers to push a change to Production.
When you write Unit Tests for a stored procedure or function, it should just verify that discrete unit of code. They should be isolated and not be impacted by other tests. The Unit Test suite should confirm that all functionalities work individually.
Why is SQL Unit Testing required?
Data is hard to manage and databases are hard to test. Database deployments are far more complicated than application deployment, since you cannot redeploy your code from scratch every time. It cannot be wiped off and recreated during deployments. Having Unit Tests gives you a lot of confidence in making a functionality change and getting an immediate feedback.
Database Lifecycle Management Overview
Database Lifecycle Management is a systematic approach to make database changes more reliable and respond quickly to business demands. With the right set of tools and processes, developers can make complex database changes and deploy them to Production confidently. It is more about identifying defects earlier in the process, preferably prior to code check-in to source control. This is cost-effective and reduces the overhead of handling bugs later in the process. Debugging, Unit Testing and Static Code Analysis empowers a developer towards a smooth delivery.
What is Static Code Analysis?
Static Code Analysis is a collection of rules to analyze your source code at compile time to identify potential vulnerabilities, bugs, anti-patterns, refactoring and poor coding practices. It is a great way to automate your code review process, ensure that coding standards are followed and ensure a high quality product.
What is SQL Cop?
SQL Cop is a static code analysis tool to automatically detect issues with your database via SQL unit testing.
It has a number of code analysis rules that are executed against your database and an analysis report is generated with the detected issues which can be reviewed an actionized. Having a good understanding of these code analysis rules helps you to improve your development skills, since you exactly know the anti-patterns which needs to be avoided.
How to install and use SQL Cop?
SQL Cop can be downloaded as a separate executable. There is absolutely no installation required and you can run it as and when you need to do a static code analysis on your database. You can download the executable from the LessThanDot SQLCop website.
Once you run the executable, the tool will prompt you to connect to your database instance by providing the Server and Database Name.
You will then see the SQL Cop Interface which contains the details of the tool and its usage.
On the left hand side, you will see the various categories of issues that SQL Cop can help detect. The rules are categorized under 6 groups – Code, Column, Table/Views, Indexes, Configuration and Health.
When you expand any of the rules, you will find all the instances in the code having the issue.
What does the SQL Cop Rules convey?
Let’s look at few of the SQL Cop rules and understand the usage recommendations.
- Procedures without SET NOCOUNT ON
When you execute a T-SQL query, SQL Server sends the number of affected rows back to the client. This message is beneficial for the user.
However, when you execute a stored procedure performing a lot of updates/deletes/inserts, there is no need of sending the rows affected back to the client. By adding a simple SET NOCOUNT ON, you can suppress this message. This reduces network traffic and enhances the application performance.
Doing a SET NOCOUNT ON within stored procedures is considered to be a good practice from performance standpoint. SQL COP scans your entire database code and will identify the stored procedures which did not have NOCOUNT set as ON.
- Tables without Primary Key
The center pane of the SQL Cop tool displays the details around the rule.
SQL Cop inspects your database and promptly displays the tables which does not have a primary key.
- Deprecated Features
When Microsoft marks a feature as Deprecated, you should stop using it for new development work and also plan to remove it in near future. After a couple of releases, the deprecated features are discontinued and if you have usages of that syntax it will start throwing errors.
Unqualified Joins are a good example of deprecated syntax.
SQL Server normally handles ‘old-style’ join syntax quite well in most cases. However, in certain scenarios, the use of an ‘unqualified’ join syntax can cause poor query performance.
|
1 2 3 4 5 |
select a.id, substring(b.name,1,40) as 'table name', count(colid) as 'count' from syscolumns a, sysobjects b where a.id = b.id group by a.id, b.name |
The usage of explicit JOIN syntax is recommended in all cases. SQL Cop helps you to find such deprecated syntax and more in your database code.
- Fragmented Indexes
Fragmented indexes are an overhead for index maintenance. When there are a lot of updates/deletes/inserts on a table over a period of time, indexes tend to get fragmented and can cause performance bottlenecks. SQL Cop helps you to get the list of fragmented indexes in your database, which can then address by doing an index rebuild or reorganize.
- Varchar size problem
When you double click on your selected rule in the left hand pane, SQL Cop will display additional information about the rule, how to detect and correct the issue.
What are the additional functionalities that SQL Cop toolbar provides?
SQL Cop has a toolbar which provides additional functionalities like –
- Changing Database Connection
- Microsoft SQL Server Forum
- Donate to LessThanDot via Paypal
- Generate Summary Report
- Print Report
- SQL Cop Help
What permissions are required to run SQL Cop?
Behind the scene, SQL Cop uses a bunch of Dynamic Management Views to extract the information for the specific rules and display it to the users.
To execute Dynamic Management Views, you will need to have VIEW SERVER STATE or VIEW DATABASE STATE permission. If you do not have this access, SQL Cop will prompt you the below message stating the same.
How does SQL Cop work?
SQL Cop has a set of predefined rules to identify poor coding practices. It performs static code analysis on your entire database code and finds out all the instances in your database which indicate anti-patterns for database development. To achieve this, it executes T-SQL queries against your database and also captures lots of information from the Dynamic Management Views – which are great resource to monitor the health of the server and diagnose issues.
Let’s say you want to look at all the Fragmented Indexes in your database. When you expand the rule, it will fire a query to fetch the impacted indexes.
You can look at the SQL Query being executed using SQL Profiler.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
If Exists(Select cmptlevel from master.dbo.sysdatabases Where dbid = db_ID() And cmptlevel > 80) If Exists(Select 1 From fn_my_permissions(NULL, 'DATABASE') WHERE permission_name = 'VIEW DATABASE STATE') Exec('SELECT OBJECT_NAME(OBJECT_ID) + ''.'' + s.name As ProblemItem FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N''LIMITED'') d join sysindexes s ON d.OBJECT_ID = s.id and d.index_id = s.indid Where avg_fragmentation_in_percent >= 30 And OBJECT_NAME(OBJECT_ID) + ''.'' + s.name > '''' And page_count > 1000 Order By Object_Name(OBJECT_ID), s.name') Else Select 'You do not have VIEW DATABASE STATE permissions within this database' As ProblemItem Else Select 'Unable to check index fragmentation when compatibility is set to 80 or below' As ProblemItem |
How to configure SQL Profiler for SQL Cop?
If you want to capture the queries executed behind the scenes by SQL Cop, you can do that by using SQL Profiler. You can apply a filter to ‘ApplicationName’ property as ‘SQL Cop’ and it will help you to segregate queries executed by this tool only and not capture any other un-related queries running against your database instance.
Does running SQL Cop create temporary performance issues?
Running SQL Cop against your database does not create any performance issues. There are no new objects that are created by this tool in your database.
Once you provide your database details and connect to the required instance, SQL Cop does not execute all the queries required to verify the rules initially. It follows the pattern of Deferred Execution – which means that the queries are not executed by itself. The queries are executed when the user goes to the left hand pane and expands a particular rule.
Let’s say you want to look at the Tables with text/ntext. When you expand this rule, there is a query fired against your database to find out this information.
Using SQL Profiler, you can capture the required query –
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT SCHEMA_NAME(o.uid) + '.' + o.name + '.' + col.name AS ProblemItem FROM syscolumns col INNER JOIN sysobjects o ON col.id = o.id INNER JOIN systypes ON col.xtype = systypes.xtype WHERE o.type = 'U' AND OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 AND systypes.name IN ( 'text', 'ntext' ) ORDER BY SCHEMA_NAME(o.uid) , o.name , col.name; |
If all the queries were executed at a single go at the beginning it could have the potential of creating a temporary performance bottleneck. But since the rules are executed selectively as and when the user selects a rule, there is minimal or no impact on performance. However it might be a good idea to be cautious when using SQL Cop on large databases and being aware of the queries that the tool executes.
Usability issues with SQL Cop
I did see some issues while using the tool, where it gets hung when I change my database connection few times.
At other times, there was some flickering on the main tool interface which contains the rule details. Also double clicking on the various issues category on the left pane, sometimes does not display the rule details in the main window. In each of the scenarios, closing the tool and restarting it helped.
SQL Cop rules are guidelines for identifying anti-patterns in your database code, and there might be some noise/ false positives based on the project you are working one. So, it is highly recommended to properly analyze the findings from this tool and actionize accordingly.
From a DevOps perspective, at this point, there is no option to run SQL Cop as part of the automated build. It’s always beneficial to have a static code analyzer tied to your gated check-in process, so that it can reject the code changes if the quality standards are not met.
Conclusion
Code Quality plays an integral part in Software Development Lifecycle. The earlier we identify issues in the process, the easier and cheaper it is to address them. With the combination of SQL unit testing and Static Code Analysis, developers can get an early feedback for their code changes. Tools like SQL Cop are an easy way to identify anti-patterns in your SQL Server database and should be run regularly to improve overall quality of code, adhere to good practices and keep your technical debt in check.
SQL Cop rules are integrated with ApexSQL Unit Test – which helps you to write and manage SQL Database unit tests from inside the SQL Server Management Studio. You can add a predefined set of SQL Cop rules to your database for the purpose of static code analysis.
Samir is a frequent speaker at conferences such as PASS Summit, IT/Dec Connections, CodeStock, SQL Saturdays and CodeCamps. He is the Co-Chapter Lead of the Steel City SQL Server UserGroup, Birmingham, AL. He is the author of www.dotnetvibes.com
View all posts by Samir Behara
- What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more - December 26, 2017
- Review of SQL Cop for SQL unit testing - September 29, 2017
- Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461 - September 8, 2017