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.
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 (
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
- Usage of
* in the
SELECT clause is not recommended, for two reasons. First, it decreases readability. Second, if columns retrieved in
* are not used by the application, performance is degraded.
All the stored procedures that use
* 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
BY clause, it is advised to use
tablename.columnname instead of numbers to identify the
BY sequence. Use
tablename.columnname3, instead of
3. This method is good for readability as well. It will be consistent with other SELECT statements where columns are used in the
BY clause but are not used in the
SELECT clause. To list all the stored procedures which have an
BY clause, run the stored procedure displayed in listing 1 as follows:
- Dynamic queries reduce the performance of stored procedures, as usage of
sp_executesql forces recompilation of the stored procedure. To list all the Stored Procedures which have an
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:
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
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.
This article has shown a list of tips for enforcing coding standards in SQL stored procedures.
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.
Please login to rate or to leave a comment.