Best practices for writing SQL stored procedures
Below are some of the best practices / code conventions for writing SQL stored procedures.
1. One function per stored procedure
Always make sure that a stored procedure does only one task. If you are having a stored procedure doing multiple tasks split them.
Reason: This makes the code modular, reusable and easier to maintain.
2. Don’t use *
Use full column names in select statement instead of using *.
Reason: The main advantage is that you can limit the query to fetch only the columns needed, instead of all columns. If you are using *, the SQL server will replace the * with all the column names and execute the query. Using column names speeds up the process. If you use column names instead of *, you don’t have to worry about the change in data when a new column is added.
3. Use schema
Always use schema before table names such as dbo.mytable.
Reason: It allows you to add a table with same name in another schema without having to worry about altering stored procedures. Also another reason is that SQL Server will not search outside of the schema making the query faster[ref].
4. Table identifiers in joins
In joins, always refer columns with table identifiers.
Reason: You can be sure that adding new columns in a table will not break any new stored procedures. It also boosts the performance a little bit.
5. Use Convert instead of Format
When converting DATETIME use CONVERT instead of FORMAT wherever possible.
Reason: FORMAT uses more resources than CONVERT. This article by Aaron Bertrand explains it in details. Here is a performance comparison chart from the article.
6. Use of NOLOCK
Use NOLOCK under appropriate conditions. NOLOCK reduces deadlock at an increased chance of getting an uncommitted transaction.
Reason: Although NOLOCK is generally discouraged in the industry, there are areas where they can be used to improve performance i.e) reduce deadlocks. As I mentioned in my stack overflow answer,
NOLOCK can be highly useful when you are reading old data from a frequently used table. NOLOCK is also useful when dirty reads are not a problem and data is not frequently modified such as while reading list of countries, currencies, etc...
7. Use exact datatype
Use the appropriate datatype for each column. I have seen databases where VARCHAR(255) is used for Postal Codes. A waste of space. Use the appropriate datatype and size for each column to reduce the space occupied by the database.
8. Naming conventions
Name your stored procedures in a meaningful way that everyone can understand and not just you. My personal favourite is using table name along with the operation. Example: CountryListGet, CountrySave, etc…
9. Try catch
Using Try Catch inside the stored procedure is a useful way to return any SQL errors in the way you desire. Also a try catch can be useful to end the transactions within a stored procedure. In the catch statement, you can construct an Error message and raise it.
10. One declaration / condition per line
Although there is no performance boost for this practice. It makes the code easier to read and edit.
11. Use NVARCHAR instead of VARCHAR
While it is true that NVARCHAR occupies twice as much as space as VARCHAR, the support for UNICODE characters is important. This is very important if your are obtaining inputs from people and storing it in database. You don’t want to display square boxes or question marks in your application.