Published: 11 Oct 2007
By: Pinal Dave

A list of tips for enforcing coding standards in SQL.

Introduction

Each organization has its own coding standards and enforcement rules. It is sometime difficult for DBAs to change code following code review, as it may affect many different layers of the application. In large organizations, many stored procedures are written and modified every day. It is smart to keep watch on all stored procedures, at frequent intervals, before code comes to final code review. Pre-code reviewing in this manner will save lots of time. I run a few scripts every day to check the status of the all stored procedures on our development server. Doing so gives me a good indication about which stored procedures are not up to coding standards. 

Tips

The document for stored procedure coding standards is long. I have identified a few rules which I think are the most important. All the examples in this article are written assuming the DBA is a member of the System Administrator (sa) role.

I use the following script to search in all stored procedures.

Listing 1: SQL Server 2000 Example using systemtables.

  • All the columns in the SELECT clause must follow the two-part naming convention: tablename.columname. This will prevent ambiguity when searching for that column in all the stored procedures. If you use the stored procedure shown above to search for tablename.columnname it will give a more accurate result than columnname only.
  • Usage of * in the SELECT clause is not recommended, for two reasons. First, it decreases readability. Second, if columns retrieved in SELECT * are not used by the application, performance is degraded.

All the stored procedures that use SELECT * can be listed using the stored procedure displayed in listing 1, as follows:

The result set will return all the stored procedures which have used * anywhere in the text. Using this method is much faster than going through all the stored procedures and determining which ones use the '*' character.

  • In the ORDER BY clause, it is advised to use tablename.columnname instead of numbers to identify the ORDER BY sequence. Use ORDER BY tablename.columnname1, tablename.columnname2, tablename.columnname3, instead of ORDER BY 1, 2, 3. This method is good for readability as well. It will be consistent with other SELECT statements where columns are used in the ORDER BY clause but are not used in the SELECT clause. To list all the stored procedures which have an ORDER BY clause, run the stored procedure displayed in listing 1 as follows:
  • Dynamic queries reduce the performance of stored procedures, as usage of EXEC or sp_executesql forces recompilation of the stored procedure. To list all the Stored Procedures which have an EXEC or sp_executesql clause, run the stored procedure displayed in listing 1 as follows:
  • Formatting is important, but excessive spaces between different clauses makes it harder to search in the database by running a query on system tables.

Likewise, if you wish to search for any keyword or restricted word defined in your company coding standard, the above stored procedure can be used to quickly make a list of stored procedures using them. Although this example searches for only one word, it can be easily modified to search for multiple words in a stored procedure.

When searching the list of stored procedures, I usually run the following code, which generates the commands that return the text of a stored procedure. This is an easy way to see all the stored procedures in the result pane. In this way, each stored procedure does not have to be opened to view its text; or run sp_helptext to see it.

Listing 2 shows a stored procedure called usp_SearchStoredProcedure_GenerateHelpText, which generates the code to display the text of searched stored procedures.

Listing 6: Stored procedure to retrieve the text of searched stored procedures.

First, change the results to display in text. Then, run stored procedure in listing 2, as follows:

Result Set:

Notice that the result set contains the object prefixed with the object owner. This is required for sp_helptext to execute without any error. If you want to skip this step and go to the next step where you can see the text of the stored procedure, all you need to do is run the stored procedure in listing 3, usp_SearchStoredProcedure_Generate_ViewSPText. This generates the text of all the searched stored procedures.

Listing 9: Stored procedure that generates the text of all the stored procedures.

First, change the results to display in text. Then, run the procedure in listing 3 as follows:

This stored procedure will display the code text of all the stored procedures in the results window.  These tricks will help Senior DBAs to do code reviews faster for all stored procedures.

Another interesting fact to note about listing 1: If you run the SQL Server 2000 version and SQL Server 2005 together in the same transaction, the performance is quite noticeable. In the SQL Server 2005 version, the query cost is 8% (relative to the batch). As shown in figure 1, in the SQL Server 2000 version the query cost is 92% (relative to the batch). Another reason to migrate to SQL Server 2005!

Figure 1: Cost of SQL Server query

SqlImage1.gif

Listing 11: Find created/modified stored procedures.

  • Sometimes it helps to check how many stored procedures are changed and created since last code review. I create reports of changed/created stored procedures and visually inspect a few of the stored procedure. Create the stored procedure shown in listing 4 and execute it as follows to find created/modified stored procedures in the last 10 days:

You can pass any other value instead of 10. The above stored procedure will list all user-created stored procedures as well as all system stored procedures. If a stored procedure is not modified after it was created, the modify and create dates will be the same.

There are few additional things should be quickly visually inspected.

  • Important logic in stored procedures and functions should be properly documented.
  • If possible, all the tables should be joined on indexed columns. That improves performance significantly.
  • Table names should be aliased for readability. I like to alias table names; however, every organization have their policy about table aliases.
  • All newly created tables should have a primary key and a clustered index.

The rules mentioned here are not final rules and they can be modified as per organization policy and business requirements. These rules are for pre-code review. Final code review may include all the rules of the pre-code review, including coding standards and performance testing.

Summary

This article has shown a list of tips for enforcing coding standards in SQL stored procedures.

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Pinal Dave

Pinalkumar Dave is Microsoft SQL Server MVP and author of several hundreds SQL Server articles. He has six years experience as Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NE...

This author has published 16 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category


Lucene.NET vs SQL Server Full-text – Generating a million records and a full-text index
In this article we will take a look at how SQL Server performs with one million records in a table. ...
Identifying currently running SQL queries
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
What's blocking my running SQL?
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
Easy Insert, Update and Retrieve Values for Microsoft SQL Database with C#, Visual Studio
This article will provide you with the small amount of code required to insert, update and retrieve ...
SQL Azure to Developers: Part 1
In this part we will focus on overview of SQL Azure along with a first look on SQL Azure Management ...

You might also be interested in the following related blog posts


Data-binding Telerik CoverFlow for Silverlight + some Routed Commands goodness read more
November's Toolbox Column Now Online read more
Postgresql - Day 2 read more
Migrating to Postgresql with my friend NHibernate read more
How do I deploy an application and its prerequisites? (Mary Lee) read more
Migrated from Community Server to DasBlog read more
Adding users to a TFS project when youre not on the domain read more
October's Toolbox Column Now Online read more
ObjectContext.SaveChanges is now Virtual/Overridable in EF4 read more
Web Deployment Tool has gone RTW read more
Top
 
 
 

Discussion


Subject Author Date
placeholder dynamic queries Jeffrey Hamby 1/3/2008 9:58 AM
RE: dynamic queries Sonu Kapoor 1/3/2008 10:00 AM

Please login to rate or to leave a comment.