Avoid descriptive prefixes

Another common and redundant habit is the use of prefixes that describe the type of object being named. The use of “tbl” for tables, “vw” for views and “sp” for stored procedures sheds no light on the object in question and serves only to lengthen the name without referencing the data model.

Programming conventions

Uppercase for key words

Key words and reserved SQL words should always appear in uppercase. This not only increases the legibility of the statement, but also punctuates the statement into clauses defined by syntax.

Minimise the use of CamelCase

Unless a word naturally uses camel case, it is best to avoid it – using underscores instead, if necessary. Restrict the use of camel case to naming function, procedures, tables and views. For column names, use underscores instead. This is largely a matter of style, but it does minimise the problem of naturally-occurring uppercase names such as acronyms like “URL”. This is particularly important when using a case-sensitive collation, as clickedURL is not the same as clickedUrl, for instance.

Use white space

There are two particularly good uses of white space in SQL statements. One is to use line-breaks before each clause, and the other is to use spaces between operators.

Avoid proprietary statements

If a standard keyword or statement is available, always use it in preference to a proprietary alternative. The same goes for abbreviated keywords such as “INT” instead of “INTEGER”. This increases the portability of the database and more readable to other developers.

Avoid UNIONs

More often than not, a UNION will not be optimised due to a high level of redundancy. Unless you are using UNION ALL, it is often better to pass this logic to the application to ensure that it is handled as expected. A UNION shouldn’t ever need to reference the same table twice, either, when CASE and OR statements usually do the job more efficiently.

Avoid temporary tables

Not all database types support the creation of new tables on-the-fly. In many situations, the creation of tables requires administrative privileges not granted to the application login.

Avoid stored procedures

This is a tricky one. Unless all your supported databases themselves support stored procedures, you aren’t going to be able to use them effectively. Instead, use stored statements within class functions that are embedded in transactions and/or data locking.

Avoid using cursors

Procedural programming, particularly in stored procedures, is a high level function that is not supported by all DBMS systems. While there are often good reasons to use cursors, it is usually better to transfer the logic back into the application to maintain maximum portability.